判断插入值是否重复:
CREATE PROCEDURE IR_rb_User_UpdateUser
(
@UserID int,
@Name nvarchar(50),
@Email nvarchar(100),
@Password nvarchar(20),
@RoleID int,
@iSuccess int output
)
AS
set @iSuccess = -1 --判断是否成功有错误应为插入重复键
IF EXISTS (SELECT Email FROM rb_Users WHERE Email = @Email and UserID<>@UserID)
BEGIN
set @iSuccess = -2
return
END
BEGIN TRAN
UPDATE rb_Users SET Name = @Name, Email = @Email,
Password = @Password WHERE UserID = @UserID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END
set @iSuccess = -3
update rb_UserRoles set RoleID=@RoleID where UserID=@UserID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END
COMMIT TRAN
set @iSuccess = 1
GO
条件查询语句:
(
@Type int ,
@UserID varchar ( 20 )
)
AS
declare @strsql varchar ( 1500 ) -- 临时sql语句
declare @strwhere varchar ( 1000 ) -- 临时sql的条件语句
set @strsql = ''
set @strsql = @strsql + ' select UserID,Role,(select typename from SysAuthorizationType where type=t.type) as typeTmp, '
set @strsql = @strsql + ' (CASE WHEN State = 1 THEN ' + ''' 有效 ''' + ' ELSE ' + ''' 无效 ''' + ' END) AS StateTmp '
set @strsql = @strsql + ' FROM SysAuthorization t '
set @strwhere = ''
IF @UserID <> ''
BEGIN
set @strwhere = @strwhere + ' and (UserID LIKE ' + '''' + ' % ' + @UserID + ' % ' + '''' + ' ) '
END
IF @Type > 0
BEGIN
set @strwhere = @strwhere + ' and Type = ' + CAST ( @Type AS varchar ( 12 ))
END
if @strwhere <> ''
BEGIN
set @strsql = @strsql + ' where ' + right ( @strwhere , len ( @strwhere ) - 4 )
END
-- 按用户名排序
set @strsql = @strsql + ' ORDER BY Type,UserID '
exec ( @strsql )
GO