判斷插入值是否重復:
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
條件查詢語句:
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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
