存储过程带事务，拼接id，返回值
以下SQL以防以后还需用到，特此备份
删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下SQL
1 ALTER PROCEDURE [dbo].[proc_tb_leaveword_delete] 2 ( 3 @leavewordID INT, 4 @record TINYINT OUTPUT 5 ) 6 AS 7 BEGIN 8 BEGIN TRY 9 BEGIN TRANSACTION 10 DELETE FROM tb_leavewordID WHERE leavewordID=@leavewordID 11 DELETE FROM tb_reply WHERE leavewordID=@leavewordID 12 SET @record=0 --成功 13 COMMIT TRANSACTION 14 END TRY 15 BEGIN CATCH 16 ROLLBACK TRANSACTION 17 SET @record=-1 --失败 18 END CATCH 19 RETURN @record 20 END
删除一条新闻，一条新闻可能有多条留言，每条留言可能有回复信息，这时我们删除一条新闻的SQL如下
1 ALTER PROCEDURE [dbo].[proc_tb_news_delete] 2 ( 3 @newsID INT, 4 @record TINYINT OUTPUT 5 ) 6 AS 7 BEGIN 8 DECLARE @leavewordCount INT --留言个数 9 DECLARE @delete_where VARCHAR(4000) --留言id字符，类似1,2,4,5,6 10 SET @leavewordCount=(SELECT ISNULL(COUNT(1),0) FROM tb_leaveword WHERE newsID=@newsID) 11 SET @delete_where='' 12 13 IF(@leavewordCount=0) --此条新闻无留言时 14 BEGIN TRY 15 DELETE FROM tb_news WHERE newsID=@newsID 16 SET @record=0 --成功 17 END TRY 18 BEGIN CATCH 19 SET @record=-1 --失败 20 END CATCH 21 22 ELSE IF(@leavewordCount>0) --此条新闻有留言时 23 ----获取删除条件(start)---- 24 DECLARE MY_CURSOR CURSOR 25 FOR SELECT leavewordID FROM tb_news WHERE newsID=@newsID 26 BEGIN 27 DECLARE @leavewordID INT 28 OPEN MY_CURSOR 29 FETCH NEXT FROM MY_CURSOR INTO @leavewordID 30 IF(@leavewordID IS NOT NULL) 31 SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+',' 32 WHILE(@@FETCH_STATUS<>-1) 33 BEGIN 34 SET @leavewordID=NULL 35 FETCH NEXT FROM MY_CURSOR INTO @leavewordID 36 IF(@leavewordID IS NOT NULL) 37 SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+',' 38 END 39 END 40 CLOSE MY_CURSOR 41 DEALLOCATE MY_CURSOR 42 SET @delete_where=SUBSTRING(@delete_where,1,LEN(@delete_where)-1) 43 ----获取删除条件(end)---- 44 BEGIN 45 BEGIN TRY 46 BEGIN TRANSACTION 47 DELETE FROM tb_news WHERE newsID=@newsID 48 EXECUTE('DELETE FROM tb_leaveword WHERE leavewordID IN('+@delete_where+')') 49 EXECUTE('DELETE FROM tb_reply WHERE leavewordID IN('+@delete_where+')') 50 SET @record=0 --成功 51 COMMIT TRANSACTION 52 END TRY 53 BEGIN CATCH 54 ROLLBACK TRANSACTION 55 SET @record=-1 --失败 56 END CATCH 57 END 58 RETURN @record 59 END
删除一新闻类型时，可能此类型下有多条新闻，此条新闻下又有多条留言，留言下又有多条回复，依次级联删除，如下存储过程
1 ALTER PROCEDURE [dbo].[proc_tb_news_type_delete] 2 ( 3 @typeID INT, 4 @record TINYINT OUTPUT 5 ) 6 AS 7 BEGIN 8 DECLARE @newsCount INT --此类新闻下的新闻个数 9 SET @newsCount=(SELECT ISNULL(COUNT(1),0) FROM tb_news WHERE typeID=@typeID) 10 IF(@newsCount=0) --此类型下无新闻 11 BEGIN TRY 12 DELETE FROM tb_news_type WHERE typeID=@typeID 13 SET @record=0 --成功 14 END TRY 15 BEGIN CATCH 16 SET @record=-1 --失败 17 END CATCH 18 19 ELSE IF(@newsCount>0) --此类型下有新闻 20 BEGIN TRY 21 BEGIN TRANSACTION 22 DECLARE MY_CURDOR CURSOR 23 FOR SELECT newsID FROM tb_news WHERE typeID=@typeID 24 BEGIN 25 DECLARE @newsID INT 26 OPEN MY_CURSOR 27 FETCH NEXT FROM MY_CURSOR INTO @newsID 28 IF(@newsID IS NOT NULL) 29 DELETE FROM tb_news_type WHERE typeID=@typeID 30 EXECUTE proc_tb_news_delete @newsID=@newsID --执行存储过程 31 WHILE(@@FETCH_STATUS<>-1) 32 BEGIN 33 SET @newsID=NULL 34 FETCH NEXT FROM MY_CURSOR INTO @newsID 35 IF(@newsID IS NOT NULL) 36 DELETE FROM tb_news_type WHERE typeID=@typeID 37 EXECUTE proc_tb_news_delete @newsID=@newsID --执行存储过程 38 END 39 END 40 CLOSE MY_CURSOR 41 DEALLOCATE MY_CURSOR 42 COMMIT TRANSACTION 43 END TRY 44 BEGIN CATCH 45 ROLLBACK TRANSACTION 46 SET @record=-1 --失败 47 END CATCH 48 RETURN @record 49 END
当删除多条新闻类型时，我们需要把拼接好的类型id，例如：1,2,4,5,12,34,穿入存储过程，分割字符的SQL语句如下所示：
1 DECLARE @A VARCHAR(5000) 2 DECLARE @i INT 3 SET @A='A,B,C,D,D,S,X,C,C,C,D,AAAA,DDDDDD,DEF,ERT,' 4 SET @i=CHARINDEX(',',@A) 5 6 WHILE @i>=1 7 BEGIN 8 PRINT LEFT(@A,@i-1) 9 SET @A=SUBSTRING(@A,@i+1,LEN(@A)-1) 10 SET @i=CHARINDEX(',',@A) 11 END
删除多条新闻类型SQL如下：
1 ALTER PROCEDURE [dbo].[proc_tb_news_type_selects_delete] 2 ( 3 @typeID_list VARCHAR(500), 4 @record TINYINT OUTPUT 5 ) 6 AS 7 BEGIN 8 BEGIN TRY 9 BEGIN TRANSACTION 10 DECLARE @index INT 11 DECLARE @typeID INT 12 SET @typeID_list=RTRIM(LTRIM(@typeID_list)) 13 SET @index=CHARINDEX(',',@typeID_list) 14 WHILE @index>=1 15 BEGIN 16 SET @typeID=CAST(LEFT(@typeID_list,@index-1) AS INT) 17 EXECUTE proc_tb_news_type_delete @typeID=@typeID 18 SET @typeID_list=SUBSTRING(@typeID_list,@index+1,LEN(@typeID_list)-1) 19 SET @index=CHARINDEX(',',@typeID_list) 20 END 21 COMMIT TRANSACTION 22 SET @record=0 --成功 23 END TRY 24 BEGIN CATCH 25 ROLLBACK TRANSACTION 26 SET @record=-1 --失败 27 END CATCH 28 RETURN @record 29 END
随机生成大写字母字符串
大写字母65-90 小写字母97-122
DECLARE @random INT DECLARE @i INT DECLARE @az VARCHAR(8) SET @i=1 SET @az=CHAR(FLOOR(RAND()*26)+65) WHILE @i<8 BEGIN SET @i=@i+1 SET @az=@az+CHAR(FLOOR(RAND()*26)+65) END PRINT @az
如需转载，请注明出处
