sqlserver 存储过程带事务 拼接id 返回值

各位用户为了找寻关于sqlserver 存储过程带事务 拼接id 返回值的资料费劲了很多周折。这里教程网为您整理了关于sqlserver 存储过程带事务 拼接id 返回值的相关资料，仅供查阅，以下为您介绍关于sqlserver 存储过程带事务 拼接id 返回值的详细内容

删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下SQL

代码如下:

ALTER PROCEDURE [dbo].[proc_tb_leaveword_delete]  (       @leavewordID INT,      @record TINYINT OUTPUT  )      AS  BEGIN      BEGIN TRY          BEGIN TRANSACTION              DELETE FROM tb_leavewordID WHERE leavewordID=@leavewordID              DELETE FROM tb_reply WHERE leavewordID=@leavewordID              SET @record=0 --成功              COMMIT TRANSACTION      END TRY      BEGIN CATCH          ROLLBACK TRANSACTION          SET @record=-1 --失败      END CATCH      RETURN @record  END

删除一条新闻，一条新闻可能有多条留言，每条留言可能有回复信息，这时我们删除一条新闻的SQL如下

代码如下:

ALTER PROCEDURE [dbo].[proc_tb_news_delete]  (       @newsID INT,      @record TINYINT OUTPUT  )      AS  BEGIN      DECLARE @leavewordCount INT --留言个数      DECLARE @delete_where VARCHAR(4000) --留言id字符，类似1,2,4,5,6      SET @leavewordCount=(SELECT ISNULL(COUNT(1),0) FROM tb_leaveword WHERE newsID=@newsID)      SET @delete_where=''      IF(@leavewordCount=0) --此条新闻无留言时          BEGIN TRY              DELETE FROM tb_news WHERE newsID=@newsID              SET @record=0 --成功          END TRY          BEGIN CATCH              SET @record=-1 --失败          END CATCH      ELSE IF(@leavewordCount>0) --此条新闻有留言时         ----获取删除条件(start)----         DECLARE MY_CURSOR CURSOR         FOR SELECT leavewordID FROM tb_news WHERE newsID=@newsID         BEGIN             DECLARE @leavewordID INT             OPEN MY_CURSOR             FETCH NEXT FROM MY_CURSOR INTO @leavewordID             IF(@leavewordID IS NOT NULL)                 SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+','                 WHILE(@@FETCH_STATUS<>-1)                     BEGIN                         SET @leavewordID=NULL                         FETCH NEXT FROM MY_CURSOR INTO @leavewordID                         IF(@leavewordID IS NOT NULL)                             SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+','                     END          END          CLOSE MY_CURSOR          DEALLOCATE MY_CURSOR          SET @delete_where=SUBSTRING(@delete_where,1,LEN(@delete_where)-1)          ----获取删除条件(end)----          BEGIN              BEGIN TRY                  BEGIN TRANSACTION                      DELETE FROM tb_news WHERE newsID=@newsID                      EXECUTE('DELETE FROM tb_leaveword WHERE leavewordID IN('+@delete_where+')')                      EXECUTE('DELETE FROM tb_reply WHERE leavewordID IN('+@delete_where+')')                      SET @record=0 --成功                      COMMIT TRANSACTION              END TRY              BEGIN CATCH                  ROLLBACK TRANSACTION                  SET @record=-1 --失败              END CATCH          END       RETURN @record  END

删除一新闻类型时，可能此类型下有多条新闻，此条新闻下又有多条留言，留言下又有多条回复，依次级联删除，如下存储过程

代码如下:

ALTER PROCEDURE [dbo].[proc_tb_news_type_delete]  (       @typeID INT,      @record TINYINT OUTPUT  )  AS  BEGIN      DECLARE @newsCount INT --此类新闻下的新闻个数      SET @newsCount=(SELECT ISNULL(COUNT(1),0) FROM tb_news WHERE typeID=@typeID)      IF(@newsCount=0) --此类型下无新闻          BEGIN TRY              DELETE FROM tb_news_type WHERE typeID=@typeID              SET @record=0 --成功          END TRY          BEGIN CATCH              SET @record=-1 --失败          END CATCH      ELSE IF(@newsCount>0) --此类型下有新闻          BEGIN TRY              BEGIN TRANSACTION                  DECLARE MY_CURDOR CURSOR                  FOR SELECT newsID FROM tb_news WHERE typeID=@typeID                  BEGIN                      DECLARE @newsID INT                      OPEN MY_CURSOR                      FETCH NEXT FROM MY_CURSOR INTO @newsID                      IF(@newsID IS NOT NULL)                          DELETE FROM tb_news_type WHERE typeID=@typeID                          EXECUTE proc_tb_news_delete @newsID=@newsID --执行存储过程                          WHILE(@@FETCH_STATUS<>-1)                              BEGIN                                  SET @newsID=NULL                                  FETCH NEXT FROM MY_CURSOR INTO @newsID                                  IF(@newsID IS NOT NULL)                                      DELETE FROM tb_news_type WHERE typeID=@typeID                                      EXECUTE proc_tb_news_delete @newsID=@newsID --执行存储过程                              END                  END                  CLOSE MY_CURSOR                  DEALLOCATE MY_CURSOR                  COMMIT TRANSACTION          END TRY          BEGIN CATCH              ROLLBACK TRANSACTION              SET @record=-1 --失败          END CATCH       RETURN @record  END

当删除多条新闻类型时，我们需要把拼接好的类型id，例如：1,2,4,5,12,34,穿入存储过程，分割字符的SQL语句如下所示：

代码如下:

DECLARE @A VARCHAR(5000)  DECLARE @i INT  SET @A='A,B,C,D,D,S,X,C,C,C,D,AAAA,DDDDDD,DEF,ERT,'  SET @i=CHARINDEX(',',@A)  WHILE @i>=1  BEGIN      PRINT LEFT(@A,@i-1)      SET @A=SUBSTRING(@A,@i+1,LEN(@A)-1)      SET @i=CHARINDEX(',',@A)  END

删除多条新闻类型SQL如下：

代码如下:

ALTER PROCEDURE [dbo].[proc_tb_news_type_selects_delete]  (       @typeID_list VARCHAR(500),      @record TINYINT OUTPUT  )  AS  BEGIN      BEGIN TRY              BEGIN TRANSACTION                  DECLARE @index INT                  DECLARE @typeID INT                  SET @typeID_list=RTRIM(LTRIM(@typeID_list))                  SET @index=CHARINDEX(',',@typeID_list)                  WHILE @index>=1                      BEGIN                          SET @typeID=CAST(LEFT(@typeID_list,@index-1) AS INT)                          EXECUTE proc_tb_news_type_delete @typeID=@typeID                          SET @typeID_list=SUBSTRING(@typeID_list,@index+1,LEN(@typeID_list)-1)                          SET @index=CHARINDEX(',',@typeID_list)                      END              COMMIT TRANSACTION              SET @record=0 --成功      END TRY      BEGIN CATCH          ROLLBACK TRANSACTION          SET @record=-1 --失败      END CATCH      RETURN @record  END

作者：cnblogs xu_happy_you