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

180 阅读 0 评论 119 点赞

我是靠谱客的博主单薄店员，最近开发中收集的这篇文章主要介绍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