存储过程带事务，拼接id，返回值

以下SQL以防以后还需用到，特此备份

删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下SQL

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 --成功
            COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SET @record=-1 --失败
    END CATCH
    RETURN @record
END

删除一条新闻，一条新闻可能有多条留言，每条留言可能有回复信息，这时我们删除一条新闻的SQL如下

ALTER PROCEDURE [dbo].[proc_tb_news_delete]
( 
    @newsID INT,
    @record TINYINT OUTPUT
)    
AS
BEGIN
    DECLARE @leavewordCount INT --留言个数
    DECLARE @delete_where VARCHAR(4000) --留言id字符，类似1,2,4,5,6
    SET @leavewordCount=(SELECT ISNULL(COUNT(1),0) FROM tb_leaveword WHERE newsID=@newsID)
    SET @delete_where=''

    IF(@leavewordCount=0) --此条新闻无留言时
        BEGIN TRY
            DELETE FROM tb_news WHERE newsID=@newsID
            SET @record=0 --成功
        END TRY
        BEGIN CATCH
            SET @record=-1 --失败
        END CATCH

    ELSE IF(@leavewordCount>0) --此条新闻有留言时
       ----获取删除条件(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)
        ----获取删除条件(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 --成功
                    COMMIT TRANSACTION
            END TRY
            BEGIN CATCH
                ROLLBACK TRANSACTION
                SET @record=-1 --失败
            END CATCH
        END
     RETURN @record
END

删除一新闻类型时，可能此类型下有多条新闻，此条新闻下又有多条留言，留言下又有多条回复，依次级联删除，如下存储过程

ALTER PROCEDURE [dbo].[proc_tb_news_type_delete]
( 
    @typeID INT,
    @record TINYINT OUTPUT
)
AS
BEGIN
    DECLARE @newsCount INT --此类新闻下的新闻个数
    SET @newsCount=(SELECT ISNULL(COUNT(1),0) FROM tb_news WHERE typeID=@typeID)
    IF(@newsCount=0) --此类型下无新闻
        BEGIN TRY
            DELETE FROM tb_news_type WHERE typeID=@typeID
            SET @record=0 --成功
        END TRY
        BEGIN CATCH
            SET @record=-1 --失败
        END CATCH
    
    ELSE IF(@newsCount>0) --此类型下有新闻
        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 --执行存储过程
                        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 --执行存储过程
                            END
                END
                CLOSE MY_CURSOR
                DEALLOCATE MY_CURSOR
                COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION
            SET @record=-1 --失败
        END CATCH
     RETURN @record
END

 当删除多条新闻类型时，我们需要把拼接好的类型id，例如：1,2,4,5,12,34,穿入存储过程，分割字符的SQL语句如下所示：

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

删除多条新闻类型SQL如下：

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 --成功
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SET @record=-1 --失败
    END CATCH
    RETURN @record
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

如需转载，请注明出处