sqlserver 存储过程带事务 拼接id 返回值
前端之家收集整理的这篇文章主要介绍了
sqlserver 存储过程带事务 拼接id 返回值，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下sql
<div class="codetitle"><a style="CURSOR: pointer" data="1702" class="copybut" id="copybut1702" onclick="doCopy('code1702')"> 代码如下:
<div class="codebody" id="code1702">
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如下
<div class="codetitle">
<a style="CURSOR: pointer" data="93416" class="copybut" id="copybut93416" onclick="doCopy('code93416')"> 代码如下:
<div class="codebody" id="code93416">
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
