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