欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-12 22:11:28
删除一条留言信息会级联删除回复信息,这时我们需要用到事务,如下sql复制代码 代码如下:alter procedure [dbo].[proc_tb_leaveword_d...
删除一条留言信息会级联删除回复信息,这时我们需要用到事务,如下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

上一篇:

下一篇: