sqlserver 存储过程带事务 拼接id 返回值
时间:2024-01-21 02:41:18
删除一条留言信息会级联删除回复信息,这时我们需要用到事务,如下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
标签:存储过程,事务
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
MYSQL的select 学习笔记
2024-01-21 17:48:50
mysql5.58的编译安装
2011-01-29 16:26:00
python中defaultdict用法实例详解
2022-08-09 17:01:10
![](https://img.aspxhome.com/file/2023/9/109339_0s.png)
蜕变——记QQ医生3.0
2009-09-16 14:41:00
![](https://img.aspxhome.com/file/UploadPic/20099/16/0909-qqdoctor3-02-88s.jpg)
跟老齐学Python之折腾一下目录
2021-10-07 03:07:52
python实现解数独程序代码
2021-05-16 18:39:43
![](https://img.aspxhome.com/file/2023/8/101318_0s.png)
Python如何读写二进制数组数据
2022-01-12 19:35:57
如何创建SQL Server 2000故障转移群集
2009-02-13 17:18:00
如何对Python编译PyInstaller打包生成的exe文件进行反编译生成pyc、py源代码文件
2023-11-06 00:10:33
![](https://img.aspxhome.com/file/2023/6/64286_0s.png)
测试框架JUnit VS TestNG对比分析
2023-02-11 02:44:02
Ubuntu20下MySQL 8.0.28 安装卸载方法图文教程
2024-01-13 23:43:11
![](https://img.aspxhome.com/file/2023/9/116759_0s.jpg)
Python处理yaml和嵌套数据结构技巧示例
2023-10-18 04:33:37
![](https://img.aspxhome.com/file/2023/5/85145_0s.png)
python3列表删除大量重复元素remove()方法的问题详解
2021-02-02 19:26:03
Python图像处理之几何变换
2021-05-05 12:46:39
![](https://img.aspxhome.com/file/2023/0/131560_0s.png)
Vue Router中应用中间件的方法
2024-05-09 10:42:43
![](https://img.aspxhome.com/file/2023/6/126626_0s.jpg)
简单了解Python中的几种函数
2023-09-30 06:11:55
基于Python编写简单实用的日志装饰器
2022-10-09 01:21:51
![](https://img.aspxhome.com/file/2023/3/75213_0s.png)
virtualenv实现多个版本Python共存
2021-11-27 00:50:44
![](https://img.aspxhome.com/file/2023/6/64706_0s.png)
使用DW中遇到的常见问题详解
2008-03-18 16:27:00
IDEA最新激活码永久激活教程附激活失败原因汇总
2023-12-13 11:57:29
![](https://img.aspxhome.com/file/2023/5/131895_0s.png)