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