導讀:1建站知識存儲過程帶事務，拼接id，返回值 以下SQL以防以后還需用到，特此備份網站seo優化課程網站優化seo培訓。

刪除一條留言信息會級聯刪除回復信息，這時我們需要用到事務，如下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].[pr個業網站建設公司oc_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

關鍵詞標簽: 存儲過程 返回值