sqlserver 存储过程带事务 拼接id 返回值

删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下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