sql-server - sql错误处理和输出

Question

我有下面的代码，它遍历每一行并为该行中的每个值运行存储的proc。某些值将失败，并且代码将继续使用下一个值，直到它为整个表运行了SP。

我需要知道的是如何生成尝试和失败的所有行的列表。我不希望它在失败时停止，因为它应该完成，但是它应该让我知道它没有作用的rowsID，因此我可以再次为失败的行运行它。

    DECLARE @SP VARCHAR(50)
    DECLARE @ROWID INT
    DECLARE @MAXROW INT
    DECLARE @HEADOFFICECLIENTINVOICEID VARCHAR(50)
    DECLARE @OUTLETID VARCHAR(50)
    DECLARE @COMPANYINFOID VARCHAR(50)
    DECLARE @YEARID VARCHAR(50)
    DECLARE @AREAID VARCHAR(50)
    DECLARE @REPORTSTYLE VARCHAR(50)
    DECLARE @STARTDATE VARCHAR(50)
    DECLARE @ENDDATE VARCHAR(50)
    DECLARE @SQL VARCHAR(1000)

    SET @SP = 'FINANCE_BUDGETING_AGAINST_ACTUAL_REPORT'
    SET @ROWID = 1

    SELECT @MAXROW = MAX(ROWID)
    FROM #AKTEMP

    WHILE @ROWID <= @MAXROW
    BEGIN
        SELECT @HEADOFFICECLIENTINVOICEID = HEADOFFICECLIENTINVOICESID
        FROM #AKTEMP
        WHERE ROWID = @ROWID

        SELECT @OUTLETID = OUTLETID
        FROM #AKTEMP
        WHERE ROWID = @ROWID

        SELECT @COMPANYINFOID = COMPANYINFOID
        FROM #AKTEMP
        WHERE ROWID = @ROWID

        SELECT @YEARID = YEARID
        FROM #AKTEMP
        WHERE ROWID = @ROWID

        SELECT @AREAID = AREAID
        FROM #AKTEMP
        WHERE ROWID = @ROWID

        SELECT @REPORTSTYLE = REPORTSTYLE
        FROM #AKTEMP
        WHERE ROWID = @ROWID

        SELECT @STARTDATE = CONVERT(VARCHAR(11), REPORTDATEFROM, 106)
        FROM #AKTEMP
        WHERE ROWID = @ROWID

        SELECT @ENDDATE = CONVERT(VARCHAR(11), REPORTDATETO, 106)
        FROM #AKTEMP
        WHERE ROWID = @ROWID

        SET @SQL = @SP + ' @HEADOFFICECLIENTINVOICEID = ' + @HEADOFFICECLIENTINVOICEID +
                        ', @OUTLETID = ' + @OUTLETID + 
                        ', @COMPANYINFOID = ' + @COMPANYINFOID + 
                        ', @YEARID = ' + @YEARID + 
                        ', @AREAID = ' + @AREAID + 
                        ', @REPORTSTYLE = ' + @REPORTSTYLE + 
                        ', @STARTDATE = ''' + @STARTDATE + '''' + 
                        ', @ENDDATE = ''' + @ENDDATE + ''''

        PRINT @SQL
        PRINT @ROWID

        EXEC (@SQL)

        SET @ROWID = @ROWID + 1
    END

Answer 1

我使用Try Catch Error处理。如果出现错误，它将捕获表变量@TAB中的rowid。

注意:由于您的案例没有可用的架构，因此我尚未对其进行检查。

    DECLARE @SP VARCHAR(50)
    DECLARE @ROWID INT
    DECLARE @MAXROW INT
    DECLARE @HEADOFFICECLIENTINVOICEID VARCHAR(50)
    DECLARE @OUTLETID VARCHAR(50)
    DECLARE @COMPANYINFOID VARCHAR(50)
    DECLARE @YEARID VARCHAR(50)
    DECLARE @AREAID VARCHAR(50)
    DECLARE @REPORTSTYLE VARCHAR(50)
    DECLARE @STARTDATE VARCHAR(50)
    DECLARE @ENDDATE VARCHAR(50)
    DECLARE @SQL VARCHAR(1000)

    SET @SP = 'FINANCE_BUDGETING_AGAINST_ACTUAL_REPORT'
    SET @ROWID = 1

    SELECT @MAXROW = MAX(ROWID)
    FROM #AKTEMP

    DECLARE @TAB TABLE (
        SNO INT IDENTITY
        ,ROWID INT
        )

    WHILE @ROWID <= @MAXROW
    BEGIN
        BEGIN TRY
            SELECT @HEADOFFICECLIENTINVOICEID = HEADOFFICECLIENTINVOICESID
            FROM #AKTEMP
            WHERE ROWID = @ROWID

            SELECT @OUTLETID = OUTLETID
            FROM #AKTEMP
            WHERE ROWID = @ROWID

            SELECT @COMPANYINFOID = COMPANYINFOID
            FROM #AKTEMP
            WHERE ROWID = @ROWID

            SELECT @YEARID = YEARID
            FROM #AKTEMP
            WHERE ROWID = @ROWID

            SELECT @AREAID = AREAID
            FROM #AKTEMP
            WHERE ROWID = @ROWID

            SELECT @REPORTSTYLE = REPORTSTYLE
            FROM #AKTEMP
            WHERE ROWID = @ROWID

            SELECT @STARTDATE = CONVERT(VARCHAR(11), REPORTDATEFROM, 106)
            FROM #AKTEMP
            WHERE ROWID = @ROWID

            SELECT @ENDDATE = CONVERT(VARCHAR(11), REPORTDATETO, 106)
            FROM #AKTEMP
            WHERE ROWID = @ROWID

            SET @SQL = @SP + ' @HEADOFFICECLIENTINVOICEID = ' + @HEADOFFICECLIENTINVOICEID + ', @OUTLETID = ' + @OUTLETID + ', @COMPANYINFOID = ' + @COMPANYINFOID + ', @YEARID = ' + @YEARID + ', @AREAID = ' + @AREAID + ', @REPORTSTYLE = ' + @REPORTSTYLE + ', @STARTDATE = ''' + @STARTDATE + '''' + ', @ENDDATE = ''' + @ENDDATE + ''''

            PRINT @SQL
            PRINT @ROWID

            EXEC (@SQL) END
            SET @ROWID = @ROWID + 1
        END TRY

        BEGIN CATCH
            INSERT INTO @TAB
            SELECT @ROWID

            SET @ROWID = @ROWID + 1
        END CATCH
    END

    SELECT * FROM @TAB