如果需要让SQL数据库中的事务和存储过程优化，SQL语句应该如何写呢？下面就将为您介绍存储过程优化的SQL语句写法，希望对您学习SQL语句的使用有所帮助。
判断插入值是否重复： 事务和存储过程优化 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
条件查询语句： 条件查询直接放在SQL中判断 CREATE PROCEDURE IR_SysAuthorization_GetAllSysAuthorization ( @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
【编辑推荐】
SQL语句中CASE WHEN的使用实例
教您不带参数的SQL语句执行的方法
巧用GO将多次重复执行SQL语句
父子分类关系查询使用的SQL语句介绍
使用SQL语句给表的栏位添加注释