删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下SQL 复制代码 代码如下: ALTER PROCEDURE [dbo].[proc_tb_leaveword_delete] ( @leavewordID INT, @record TINYINT OUTPUT ) AS BEGIN BEGIN TRY BEG
删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下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
