sqlserver 存儲進程帶事務 拼接id 前往值。本站提示廣大學習愛好者：（sqlserver 存儲進程帶事務 拼接id 前往值）文章只能為提供參考，不一定能成為您想要的結果。以下是sqlserver 存儲進程帶事務 拼接id 前往值正文
刪除一條留言信息會級聯刪除答復信息，這時候我們須要用到事務，以下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
