導(dǎo)讀:1建站知識(shí)存儲(chǔ)過(guò)程帶事務(wù)，拼接id，返回值 以下SQL以防以后還需用到，特此備份網(wǎng)站seo優(yōu)化課程網(wǎng)站優(yōu)化seo培訓(xùn)。

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

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

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].[pr個(gè)業(yè)網(wǎng)站建設(shè)公司oc_tb_news_delete] ( @newsID INT, @record TINYINT OUTPUT ) AS BEGIN DECLARE @leavewordCount INT --留言個(gè)數(shù) 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) --此條新聞無(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í)，可能此類型下有多條新聞，此條新聞下又有多條留言，留言下又有多條回復(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 --此類新聞下的新聞個(gè)數(shù) SET @newsCount=(SELECT ISNULL(COUNT(1),0) FROM tb_news WHERE typeID=@typeID) IF(@newsCount=0) --此類型下無(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) --此類型下有新聞 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

關(guān)鍵詞標(biāo)簽: 存儲(chǔ)過(guò)程 返回值