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
复制代码 代码如下:
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