SQL Server Stored Procedures return values with transaction concatenation id

Deleting a message will cascade the reply information. At this time, we need to use the transaction, as shown in the following SQL Copy codeThe Code is as follows: 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 -- Success
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @ record =-1 -- failed
END CATCH
RETURN @ record
END

Delete a news item. A news item may have multiple messages, and each message may have a reply message. The SQL statement for deleting a news item is as follows:Copy codeThe Code is as follows: alter procedure [dbo]. [proc_tb_news_delete]
(
@ NewsID INT,
@ Record TINYINT OUTPUT
)
AS
BEGIN
DECLARE @ leavewordCount INT -- number of messages
DECLARE @ delete_where VARCHAR (4000) -- Message id, similar to 1, 2, 4, 5, 6
SET @ leavewordCount = (select isnull (COUNT (1), 0) FROM tb_leaveword WHERE newsID = @ newsID)
SET @ delete_where =''
IF (@ leavewordCount = 0) -- when this news item has no message
BEGIN TRY
Delete from tb_news WHERE newsID = @ newsID
SET @ record = 0 -- Success
END TRY
BEGIN CATCH
SET @ record =-1 -- failed
END CATCH
Else if (@ leavewordCount> 0) -- when this news item has a message
---- Get the deletion condition (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)
---- Get the deletion condition (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 -- Success
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @ record =-1 -- failed
END CATCH
END
RETURN @ record
END

When you delete a news type, there may be multiple news under this type, there are multiple messages under this news, there are multiple replies under the message, cascade deletion in turn, the following stored procedureCopy codeThe Code is as follows: alter procedure [dbo]. [proc_tb_news_type_delete]
(
@ TypeID INT,
@ Record TINYINT OUTPUT
)
AS
BEGIN
DECLARE @ newsCount INT -- number of news under such news
SET @ newsCount = (select isnull (COUNT (1), 0) FROM tb_news WHERE typeID = @ typeID)
IF (@ newsCount = 0) -- no news under this type
BEGIN TRY
Delete from tb_news_type WHERE typeID = @ typeID
SET @ record = 0 -- Success
END TRY
BEGIN CATCH
SET @ record =-1 -- failed
END CATCH
Else if (@ newsCount> 0) -- news in this type
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 -- EXECUTE the Stored Procedure
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 -- EXECUTE the Stored Procedure
END
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @ record =-1 -- failed
END CATCH
RETURN @ record
END

When deleting multiple news types, we need to put the spliced type id, such as: 1, 2, 4, 5, 12, 34, into the stored procedure. The SQL statement for splitting characters is as follows:Copy codeThe Code is as follows: DECLARE @ a varchar (5000)
DECLARE @ I INT
SET @ A = 'a, B, C, D, D, S, X, C, D, AAAA, dddddddd, DEF, ERT ,'
SET @ I = CHARINDEX (',', @)
WHILE @ I> = 1
BEGIN
Print left (@ A, @ i-1)
SET @ A = SUBSTRING (@ A, @ I + 1, LEN (@ A)-1)
SET @ I = CHARINDEX (',', @)
END

The SQL statement for deleting multiple news types is as follows:Copy codeThe Code is as follows: 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 -- Success
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @ record =-1 -- failed
END CATCH
RETURN @ record
END

Author: cnblogs xu_happy_you