SQL Server 存储过程带事务 拼接id 返回值

这篇文章主要为大家详细介绍了SQL Server 存储过程带事务 拼接id 返回值，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

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

代码如下:

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

代码如下:

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

代码如下:

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

注：关于SQL Server 存储过程带事务 拼接id 返回值的内容就先介绍到这里，更多相关文章的可以留意