提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

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

SQLServer存储事务 过程 ID 拼接 返回值
2023-06-13 09:14:36 时间
删除一条留言信息会级联删除回复信息，这时我们需要用到事务，如下SQL
复制代码代码如下:

ALTERPROCEDURE[dbo].[proc_tb_leaveword_delete]
 (
    @leavewordIDINT,
    @recordTINYINTOUTPUT
 )   
 AS
 BEGIN
    BEGINTRY
        BEGINTRANSACTION
            DELETEFROMtb_leavewordIDWHEREleavewordID=@leavewordID
            DELETEFROMtb_replyWHEREleavewordID=@leavewordID
            SET@record=0--成功
            COMMITTRANSACTION
    ENDTRY
    BEGINCATCH
        ROLLBACKTRANSACTION
        SET@record=-1--失败
    ENDCATCH
    RETURN@record
 END

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

ALTERPROCEDURE[dbo].[proc_tb_news_delete]
 (
    @newsIDINT,
    @recordTINYINTOUTPUT
 )   
 AS
 BEGIN
    DECLARE@leavewordCountINT--留言个数
    DECLARE@delete_whereVARCHAR(4000)--留言id字符，类似1,2,4,5,6
    SET@leavewordCount=(SELECTISNULL(COUNT(1),0)FROMtb_leavewordWHEREnewsID=@newsID)
    SET@delete_where=""
    IF(@leavewordCount=0)--此条新闻无留言时
        BEGINTRY
            DELETEFROMtb_newsWHEREnewsID=@newsID
            SET@record=0--成功
        ENDTRY
        BEGINCATCH
            SET@record=-1--失败
        ENDCATCH
    ELSEIF(@leavewordCount>0)--此条新闻有留言时
       ----获取删除条件(start)----
       DECLAREMY_CURSORCURSOR
       FORSELECTleavewordIDFROMtb_newsWHEREnewsID=@newsID
       BEGIN
           DECLARE@leavewordIDINT
           OPENMY_CURSOR
           FETCHNEXTFROMMY_CURSORINTO@leavewordID
           IF(@leavewordIDISNOTNULL)
               SET@delete_where=@delete_where+CAST(@leavewordIDASVARCHAR(10))+","
               WHILE(@@FETCH_STATUS<>-1)
                   BEGIN
                       SET@leavewordID=NULL
                       FETCHNEXTFROMMY_CURSORINTO@leavewordID
                       IF(@leavewordIDISNOTNULL)
                           SET@delete_where=@delete_where+CAST(@leavewordIDASVARCHAR(10))+","
                   END
        END
        CLOSEMY_CURSOR
        DEALLOCATEMY_CURSOR
        SET@delete_where=SUBSTRING(@delete_where,1,LEN(@delete_where)-1)
        ----获取删除条件(end)----
        BEGIN
            BEGINTRY
                BEGINTRANSACTION
                    DELETEFROMtb_newsWHEREnewsID=@newsID
                    EXECUTE("DELETEFROMtb_leavewordWHEREleavewordIDIN("+@delete_where+")")
                    EXECUTE("DELETEFROMtb_replyWHEREleavewordIDIN("+@delete_where+")")
                    SET@record=0--成功
                    COMMITTRANSACTION
            ENDTRY
            BEGINCATCH
                ROLLBACKTRANSACTION
                SET@record=-1--失败
            ENDCATCH
        END
     RETURN@record
 END

删除一新闻类型时，可能此类型下有多条新闻，此条新闻下又有多条留言，留言下又有多条回复，依次级联删除，如下存储过程
复制代码代码如下:
ALTERPROCEDURE[dbo].[proc_tb_news_type_delete]
 (
    @typeIDINT,
    @recordTINYINTOUTPUT
 )
 AS
 BEGIN
    DECLARE@newsCountINT--此类新闻下的新闻个数
    SET@newsCount=(SELECTISNULL(COUNT(1),0)FROMtb_newsWHEREtypeID=@typeID)
    IF(@newsCount=0)--此类型下无新闻
        BEGINTRY
            DELETEFROMtb_news_typeWHEREtypeID=@typeID
            SET@record=0--成功
        ENDTRY
        BEGINCATCH
            SET@record=-1--失败
        ENDCATCH
    ELSEIF(@newsCount>0)--此类型下有新闻
        BEGINTRY
            BEGINTRANSACTION
                DECLAREMY_CURDORCURSOR
                FORSELECTnewsIDFROMtb_newsWHEREtypeID=@typeID
                BEGIN
                    DECLARE@newsIDINT
                    OPENMY_CURSOR
                    FETCHNEXTFROMMY_CURSORINTO@newsID
                    IF(@newsIDISNOTNULL)
                        DELETEFROMtb_news_typeWHEREtypeID=@typeID
                        EXECUTEproc_tb_news_delete@newsID=@newsID--执行存储过程
                        WHILE(@@FETCH_STATUS<>-1)
                            BEGIN
                                SET@newsID=NULL
                                FETCHNEXTFROMMY_CURSORINTO@newsID
                                IF(@newsIDISNOTNULL)
                                    DELETEFROMtb_news_typeWHEREtypeID=@typeID
                                    EXECUTEproc_tb_news_delete@newsID=@newsID--执行存储过程
                            END
                END
                CLOSEMY_CURSOR
                DEALLOCATEMY_CURSOR
                COMMITTRANSACTION
        ENDTRY
        BEGINCATCH
            ROLLBACKTRANSACTION
            SET@record=-1--失败
        ENDCATCH
     RETURN@record
 END

当删除多条新闻类型时，我们需要把拼接好的类型id，例如：1,2,4,5,12,34,穿入存储过程，分割字符的SQL语句如下所示：
复制代码代码如下:
DECLARE@AVARCHAR(5000)
 DECLARE@iINT
 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
    PRINTLEFT(@A,@i-1)
    SET@A=SUBSTRING(@A,@i+1,LEN(@A)-1)
    SET@i=CHARINDEX(",",@A)
 END

删除多条新闻类型SQL如下：
复制代码代码如下:
ALTERPROCEDURE[dbo].[proc_tb_news_type_selects_delete]
 (
    @typeID_listVARCHAR(500),
    @recordTINYINTOUTPUT
 )
 AS
 BEGIN
    BEGINTRY
            BEGINTRANSACTION
                DECLARE@indexINT
                DECLARE@typeIDINT
                SET@typeID_list=RTRIM(LTRIM(@typeID_list))
                SET@index=CHARINDEX(",",@typeID_list)
                WHILE@index>=1
                    BEGIN
                        SET@typeID=CAST(LEFT(@typeID_list,@index-1)ASINT)
                        EXECUTEproc_tb_news_type_delete@typeID=@typeID
                        SET@typeID_list=SUBSTRING(@typeID_list,@index+1,LEN(@typeID_list)-1)
                        SET@index=CHARINDEX(",",@typeID_list)
                    END
            COMMITTRANSACTION
            SET@record=0--成功
    ENDTRY
    BEGINCATCH
        ROLLBACKTRANSACTION
        SET@record=-1--失败
    ENDCATCH
    RETURN@record
 END

作者：cnblogsxu_happy_you

相关文章