存储过程带事务，拼接id，返回值
以下SQL以防以后还需用到，特此备份
删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下SQL
1 ALTER PROCEDURE [dbo].[proc_tb_leaveword_delete] 2 ( 3 @leavewordID INT, 4 @record TINYINT OUTPUT 5 ) 6 AS 7 BEGIN 8 BEGIN TRY 9 BEGIN TRANSACTION10 DELETE FROM tb_leavewordID WHERE leavewordID=@leavewordID11 DELETE FROM tb_reply WHERE leavewordID=@leavewordID12 SET @record=0 --成功13 COMMIT TRANSACTION14 END TRY15 BEGIN CATCH16 ROLLBACK TRANSACTION17 SET @record=-1 --失败18 END CATCH19 RETURN @record20 END
删除一条新闻，一条新闻可能有多条留言，每条留言可能有回复信息，这时我们删除一条新闻的SQL如下
1 ALTER PROCEDURE [dbo].[proc_tb_news_delete] 2 ( 3 @newsID INT, 4 @record TINYINT OUTPUT 5 ) 6 AS 7 BEGIN 8 DECLARE @leavewordCount INT --留言个数 9 DECLARE @delete_where VARCHAR(4000) --留言id字符，类似1,2,4,5,610 SET @leavewordCount=(SELECT ISNULL(COUNT(1),0) FROM tb_leaveword WHERE newsID=@newsID)11 SET @delete_where=''12 13 IF(@leavewordCount=0) --此条新闻无留言时14 BEGIN TRY15 DELETE FROM tb_news WHERE newsID=@newsID16 SET @record=0 --成功17 END TRY18 BEGIN CATCH19 SET @record=-1 --失败20 END CATCH21 22 ELSE IF(@leavewordCount>0) --此条新闻有留言时23 ----获取删除条件(start)----24 DECLARE MY_CURSOR CURSOR25 FOR SELECT leavewordID FROM tb_news WHERE newsID=@newsID26 BEGIN27 DECLARE @leavewordID INT28 OPEN MY_CURSOR29 FETCH NEXT FROM MY_CURSOR INTO @leavewordID30 IF(@leavewordID IS NOT NULL)31 SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+','32 WHILE(@@FETCH_STATUS<>-1)33 BEGIN34 SET @leavewordID=NULL35 FETCH NEXT FROM MY_CURSOR INTO @leavewordID36 IF(@leavewordID IS NOT NULL)37 SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+','38 END39 END40 CLOSE MY_CURSOR41 DEALLOCATE MY_CURSOR42 SET @delete_where=SUBSTRING(@delete_where,1,LEN(@delete_where)-1)43 ----获取删除条件(end)----44 BEGIN45 BEGIN TRY46 BEGIN TRANSACTION47 DELETE FROM tb_news WHERE newsID=@newsID48 EXECUTE('DELETE FROM tb_leaveword WHERE leavewordID IN('+@delete_where+')')49 EXECUTE('DELETE FROM tb_reply WHERE leavewordID IN('+@delete_where+')')50 SET @record=0 --成功51 COMMIT TRANSACTION52 END TRY53 BEGIN CATCH54 ROLLBACK TRANSACTION55 SET @record=-1 --失败56 END CATCH57 END58 RETURN @record59 END如需转载，请注明出处
TAG:
