SET @record=-1 --失败
DECLARE @leavewordID INT
WHILE(@@FETCH_STATUS<>-1)
删除多条新闻类型SQL如下：
BEGIN CATCH
BEGIN TRANSACTION
SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+','
BEGIN
SET @record=-1 --失败
FOR SELECT newsID FROM tb_news WHERE typeID=@typeID
BEGIN TRY
BEGIN
)

SET @delete_where=SUBSTRING(@delete_where,1,LEN(@delete_where)-1)
CLOSE MY_CURSOR
RETURN @record
IF(@newsCount=0) --此类型下无新闻
@record TINYINT OUTPUT

SET @A=SUBSTRING(@A,@i+1,LEN(@A)-1)
DELETE FROM tb_news_type WHERE typeID=@typeID
DELETE FROM tb_news_type WHERE typeID=@typeID

复制代码 代码如下:

FETCH NEXT FROM MY_CURSOR INTO @newsID

复制代码 代码如下:

SET @delete_where=''
END TRY
SET @record=0 --成功
BEGIN CATCH
BEGIN TRANSACTION
END TRY
DELETE FROM tb_reply WHERE leavewordID=@leavewordID
ELSE IF(@leavewordCount>0) --此条新闻有留言时
END
@leavewordID INT,
BEGIN CATCH
----获取删除条件(start)----

当删除多条新闻类型时，我们需要把拼接好的类型id，例如：1,2,4,5,12,34,穿入存储过程，分割字符的SQL语句如下所示：
(
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
(
FETCH NEXT FROM MY_CURSOR INTO @leavewordID
BEGIN
END
DELETE FROM tb_leavewordID WHERE leavewordID=@leavewordID
WHILE @index>=1
SET @record=0 --成功
@typeID INT,
IF(@newsID IS NOT NULL)

AS
EXECUTE proc_tb_news_type_delete @typeID=@typeID
BEGIN
DECLARE @leavewordCount INT --留言个数
SET @record=-1 --失败

COMMIT TRANSACTION
DELETE FROM tb_news_type WHERE typeID=@typeID
RETURN @record
BEGIN
SET @typeID_list=SUBSTRING(@typeID_list,@index+1,LEN(@typeID_list)-1)
SET @i=CHARINDEX(',',@A)
BEGIN TRY
END TRY
WHILE(@@FETCH_STATUS<>-1)
END

复制代码 代码如下:

(
SET @record=0 --成功
DELETE FROM tb_news WHERE newsID=@newsID
ROLLBACK TRANSACTION
BEGIN
END
END TRY
AS
DECLARE @A VARCHAR(5000)
DECLARE @typeID INT
BEGIN
SET @record=0 --成功
BEGIN
@newsID INT,
DEALLOCATE MY_CURSOR
AS
END CATCH
SET @record=-1 --失败
FETCH NEXT FROM MY_CURSOR INTO @leavewordID
COMMIT TRANSACTION
DECLARE @delete_where VARCHAR(4000) --留言id字符，类似1,2,4,5,6
SET @typeID_list=RTRIM(LTRIM(@typeID_list))
BEGIN

删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下SQL
SET @newsID=NULL
BEGIN
删除一新闻类型时，可能此类型下有多条新闻，此条新闻下又有多条留言，留言下又有多条回复，依次级联删除，如下存储过程
IF(@leavewordID IS NOT NULL)
RETURN @record
DELETE FROM tb_news WHERE newsID=@newsID
BEGIN TRY
SET @newsCount=(SELECT ISNULL(COUNT(1),0) FROM tb_news WHERE typeID=@typeID)
END
SET @A='A,B,C,D,D,S,X,C,C,C,D,AAAA,DDDDDD,DEF,ERT,'
BEGIN
RETURN @record
SET @index=CHARINDEX(',',@typeID_list)
WHILE @i>=1
END CATCH
@record TINYINT OUTPUT
SET @typeID=CAST(LEFT(@typeID_list,@index-1) AS INT)
BEGIN TRY
END CATCH
SET @record=-1 --失败
----获取删除条件(end)----
)
)
ALTER PROCEDURE [dbo].[proc_tb_leaveword_delete]
ALTER PROCEDURE [dbo].[proc_tb_news_type_delete]
BEGIN TRANSACTION
CLOSE MY_CURSOR
OPEN MY_CURSOR
END
BEGIN CATCH

BEGIN TRY
@record TINYINT OUTPUT
SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+','
END
FOR SELECT leavewordID FROM tb_news WHERE newsID=@newsID
ALTER PROCEDURE [dbo].[proc_tb_news_type_selects_delete]
SET @leavewordCount=(SELECT ISNULL(COUNT(1),0) FROM tb_leaveword WHERE newsID=@newsID)

复制代码 代码如下:

EXECUTE proc_tb_news_delete @newsID=@newsID --执行存储过程
SET @record=0 --成功

IF(@leavewordCount=0) --此条新闻无留言时
END
BEGIN TRANSACTION
AS
END CATCH
FETCH NEXT FROM MY_CURSOR INTO @newsID
EXECUTE('DELETE FROM tb_reply WHERE leavewordID IN('+@delete_where+')')
BEGIN CATCH
ALTER PROCEDURE [dbo].[proc_tb_news_delete]
END CATCH
ROLLBACK TRANSACTION

COMMIT TRANSACTION
END

(
IF(@newsID IS NOT NULL)
OPEN MY_CURSOR
END TRY
END TRY
BEGIN TRY
IF(@leavewordID IS NOT NULL)
DECLARE @newsID INT
SET @leavewordID=NULL
@typeID_list VARCHAR(500),
PRINT LEFT(@A,@i-1)

复制代码 代码如下:

SET @record=-1 --失败
DECLARE @newsCount INT --此类新闻下的新闻个数
COMMIT TRANSACTION

END

DECLARE @index INT

EXECUTE('DELETE FROM tb_leaveword WHERE leavewordID IN('+@delete_where+')')
END CATCH
SET @index=CHARINDEX(',',@typeID_list)
DEALLOCATE MY_CURSOR
DECLARE MY_CURSOR CURSOR
)
END
BEGIN CATCH
SET @i=CHARINDEX(',',@A)
@record TINYINT OUTPUT
ELSE IF(@newsCount>0) --此类型下有新闻
DECLARE MY_CURDOR CURSOR
删除一条新闻，一条新闻可能有多条留言，每条留言可能有回复信息，这时我们删除一条新闻的SQL如下
EXECUTE proc_tb_news_delete @newsID=@newsID --执行存储过程
DECLARE @i INT