Delete a message will cascade delete response information, then we need to use the transaction, the following SQL
Copy Code code 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--Successful
COMMIT TRANSACTION
End TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @record =-1--Failed
End CATCH
Return @record
End
Delete a news, a news may have more than one message, each message may have a reply, then we delete a news of SQL as follows
Copy Code code 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 character, 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 message is not
BEGIN TRY
DELETE from Tb_news WHERE newsid= @newsID
SET @record = 0--Successful
End TRY
BEGIN CATCH
SET @record =-1--Failed
End CATCH
else IF (@leavewordCount >0)--This news message
----Get the Delete condition (start)----
DECLARE My_cursor CURSOR
For the 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 Delete 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--Successful
COMMIT TRANSACTION
End TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @record =-1--Failed
End CATCH
End
Return @record
End
Delete a news type, there may be more than this type of news, this article has more than one message, under the message there are more than one reply, cascade Delete, the following stored procedures
Copy Code code as follows:
ALTER PROCEDURE [dbo]. [Proc_tb_news_type_delete]
(
@typeID INT,
@record TINYINT OUTPUT
)
As
BEGIN
DECLARE @newsCount INT--The number of news stories under this type of 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--Successful
End TRY
BEGIN CATCH
SET @record =-1--Failed
End CATCH
ELSE IF (@newsCount >0)--News under this type
BEGIN TRY
BEGIN TRANSACTION
DECLARE My_curdor CURSOR
For the 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 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 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 we delete more than one news type, we need to put a good type ID, such as: 1,2,4,5,12,34, through the stored procedure, the split character of the SQL statement as follows:
Copy Code code as follows:
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
Delete more than one news type SQL as follows:
Copy Code code 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 (@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--Successful
End TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @record =-1--Failed
End CATCH
Return @record
End
Author: cnblogs Xu_happy_you
