出處：http://www.cnblogs.com/cmsdn/archive/2012/04/25/2469568.html 以下SQL以防以後還需用到，特此備份 刪除一條留言信息會級聯刪除回覆信息，這時我們需要用到事務，如下SQL 1 ALTER PROCEDURE [dbo].[proc_tb
出處：http://www.cnblogs.com/cmsdn/archive/2012/04/25/2469568.html
以下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
