如果需要让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
