sqlserver 存儲(chǔ)過(guò)程帶事務(wù) 拼接id 返回值

更新時(shí)間：2012年10月11日 00:59:32 作者：

存儲(chǔ)過(guò)程帶事務(wù)，拼接id，返回值以下SQL以防以后還需用到，特此備份

刪除一條留言信息會(huì)級(jí)聯(lián)刪除回復(fù)信息，這時(shí)我們需要用到事務(wù)，如下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

刪除一條新聞，一條新聞可能有多條留言，每條留言可能有回復(fù)信息，這時(shí)我們刪除一條新聞的SQL如下

復(fù)制代碼代碼如下:

ALTER PROCEDURE [dbo].[proc_tb_news_delete]
 ( 
     @newsID INT,
     @record TINYINT OUTPUT
 )    
 AS
 BEGIN
     DECLARE @leavewordCount INT --留言個(gè)數(shù)
     DECLARE @delete_where VARCHAR(4000) --留言id字符，類(lèi)似1,2,4,5,6
     SET @leavewordCount=(SELECT ISNULL(COUNT(1),0) FROM tb_leaveword WHERE newsID=@newsID)
     SET @delete_where=''
     IF(@leavewordCount=0) --此條新聞無(wú)留言時(shí)
         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) --此條新聞?dòng)辛粞詴r(shí)
        ----獲取刪除條件(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

刪除一新聞?lì)愋蜁r(shí)，可能此類(lèi)型下有多條新聞，此條新聞下又有多條留言，留言下又有多條回復(fù)，依次級(jí)聯(lián)刪除，如下存儲(chǔ)過(guò)程

復(fù)制代碼代碼如下:

ALTER PROCEDURE [dbo].[proc_tb_news_type_delete]
 ( 
     @typeID INT,
     @record TINYINT OUTPUT
 )
 AS
 BEGIN
     DECLARE @newsCount INT --此類(lèi)新聞下的新聞個(gè)數(shù)
     SET @newsCount=(SELECT ISNULL(COUNT(1),0) FROM tb_news WHERE typeID=@typeID)
     IF(@newsCount=0) --此類(lèi)型下無(wú)新聞
         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) --此類(lèi)型下有新聞
         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 --執(zhí)行存儲(chǔ)過(guò)程
                         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 --執(zhí)行存儲(chǔ)過(guò)程
                             END
                 END
                 CLOSE MY_CURSOR
                 DEALLOCATE MY_CURSOR
                 COMMIT TRANSACTION
         END TRY
         BEGIN CATCH
             ROLLBACK TRANSACTION
             SET @record=-1 --失敗
         END CATCH
      RETURN @record
 END

當(dāng)刪除多條新聞?lì)愋蜁r(shí)，我們需要把拼接好的類(lèi)型id，例如：1,2,4,5,12,34,穿入存儲(chǔ)過(guò)程，分割字符的SQL語(yǔ)句如下所示：

復(fù)制代碼代碼如下:

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

刪除多條新聞?lì)愋蚐QL如下：

復(fù)制代碼代碼如下:

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