Error Checking Dynamic Stored Procedure in SQL

Question

I have a stored procedure with 3 different UPDATE queries.

I want each query to return a value.

If it fails I do not want the procedure to COMMIT, I want it to ROLLBACK tell me which one of the three stored procedures failed.

I can do it in a non-dynamic but for some reason the same rules don't seem to apply in a dynamic query.

Here is my code and my attempt:

ALTER PROCEDURE [dbo].[ASP_XXNominalBalanceUPDATE]
     @ORGCODE VARCHAR(6),
     @NewNominal INT,
     @OldNominal INT,
     @BalMonth CHAR(6),
     @Amt Decimal(12,2),
     @FINPERIODNR INT,
     @InvDetailID INT,
     @Err INT = 0,
     @Rows INT = 0
AS 
BEGIN TRAN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'SET @Err = 0
                 SET @Rows = 0

                 UPDATE CostCodeBalance' + @ORGCODE + ' COB
                 SET COB.' + @BalMonth + '  = (SELECT COB.' + @BalMonth + ' FROM CostCodeBalance' + @ORGCODE + ' COB WHERE COB.CostCode = @OldNominal AND COB.FinPeriodNr = @FINPERIODNR) + @Amt
                 WHERE COB.CostCode = @OldNominal AND COB.FinPeriodNr = @FINPERIODNR

                 SELECT @Err = @@Error, @Rows = @@ROWCOUNT

                 UPDATE CostCodeBalance' + @ORGCODE + ' COB
                 SET COB.' + @BalMonth + ' = (SELECT COB.' + @BalMonth + ' FROM CostCodeBalance' + @ORGCODE + ' COB WHERE COB.CostCode = @NewNominal AND COB.FinPeriodNr = @FINPERIODNR) - @Amt
                 WHERE COB.CostCode = @NewNominal AND COB.FinPeriodNr = @FINPERIODNR

                 SELECT @Err = @@Error, @Rows = @@ROWCOUNT

                 UPDATE Posting' + @ORGCODE + ' PO
                 SET Nominal = @NewNominal
                 WHERE PO.Deleted = 0 AND PO.OrderRecNo = @InvDetailID AND PO.OrderType = ''SI''

                 SELECT @Err = @@Error, @Rows = @@ROWCOUNT;';

    -- Check if succeeded to find.
    If @Err = 0 AND @Rows > 0 
        COMMIT TRAN
    ELSE
        ROLLBACK TRAN

    EXEC sp_executesql @sql, N'@ORGCODE VARCHAR(6), @NewNominal INT, @OldNominal INT, @BalMonth CHAR(6), @Amt Decimal(12,2), @FINPERIODNR INT, @InvDetailID INT, @Err INT, @Rows INT', 
                             @ORGCODE, @NewNominal, @OldNominal, @BalMonth, @Amt,  @FINPERIODNR, @InvDetailID, @Err, @Rows; 

Answer 1

This is my answer which I ended up with just in case anyone else wanted to know how I resolved it.

CREATE PROCEDURE [dbo].[ASP_XXNominalBalanceUPDATE]
     @ORGCODE VARCHAR(6),
     @NewNominal INT,
     @OldNominal INT,
     @BalMonth CHAR(6),
     @Amt Decimal(12,2),
     @FINPERIODNR INT,
     @InvDetailID INT

AS 
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; --IMPORTANT TO TURN THIS ON
DECLARE @SQL NVARCHAR(MAX)

    BEGIN TRAN

        ------RUN THE 1st PROCEDURE------
        SET @SQL = N'
        UPDATE CCB
        SET CCB.' + @BalMonth + '  = (SELECT CCB2.' + @BalMonth + ' FROM CostCodeBalance' + @ORGCODE + ' CCB2 
                                      WHERE CCB2.CostCode = @OldNominal AND CCB2.FinPeriodNr = @FINPERIODNR) + @Amt
        FROM CostCodeBalance' + @ORGCODE + ' CCB
        WHERE CCB.CostCode = @OldNominal AND CCB.FinPeriodNr = @FINPERIODNR;';

        EXEC sp_executesql @sql, N'@ORGCODE VARCHAR(6), @NewNominal INT,@OldNominal INT, @BalMonth CHAR(6), @Amt Decimal(12,2), @FINPERIODNR INT, @InvDetailID INT', 
                                    @ORGCODE, @NewNominal, @OldNominal, @BalMonth, @Amt,  @FINPERIODNR, @InvDetailID; 

        ------TEST IF ANYTHING WAS UPDATED------
        IF  @@ROWCOUNT = 0 GOTO ERR_HANDLER -- IF NOT THEN ROLLBACK AND RETURN ERROR

        ------RUN THE 2nd PROCEDURE------
        SET @SQL = N'
        UPDATE CCB
        SET CCB.' + @BalMonth + ' = (SELECT CCB2.' + @BalMonth + ' FROM CostCodeBalance' + @ORGCODE + ' CCB2 
                                     WHERE CCB2.CostCode = @NewNominal AND CCB2.FinPeriodNr = @FINPERIODNR) - @Amt
        FROM CostCodeBalance' + @ORGCODE + ' CCB
        WHERE CCB.CostCode = @NewNominal AND CCB.FinPeriodNr = @FINPERIODNR;';

        EXEC sp_executesql @sql, N'@ORGCODE VARCHAR(6), @NewNominal INT,@OldNominal INT, @BalMonth CHAR(6), @Amt Decimal(12,2), @FINPERIODNR INT, @InvDetailID INT', 
                                    @ORGCODE, @NewNominal, @OldNominal, @BalMonth, @Amt,  @FINPERIODNR, @InvDetailID; 

        ------TEST IF ANYTHING WAS UPDATED------
        IF  @@ROWCOUNT = 0 GOTO ERR_HANDLER -- IF NOT THEN ROLLBACK AND RETURN ERROR

        ------RUN THE 3rd PROCEDURE------
        SET @SQL = N'
        UPDATE PO
        SET Nominal = @NewNominal
        FROM Posting' + @ORGCODE + ' PO
        WHERE PO.Deleted = 0 AND PO.OrderRecNo = @InvDetailID AND PO.OrderType = ''SI'';';

        EXEC sp_executesql @sql, N'@ORGCODE VARCHAR(6), @NewNominal INT,@OldNominal INT, @BalMonth CHAR(6), @Amt Decimal(12,2), @FINPERIODNR INT, @InvDetailID INT', 
                                    @ORGCODE, @NewNominal, @OldNominal, @BalMonth, @Amt,  @FINPERIODNR, @InvDetailID; 

        ------TEST IF ANYTHING WAS UPDATED------                        
        IF  @@ROWCOUNT = 0 GOTO ERR_HANDLER -- IF NOT THEN ROLLBACK AND RETURN ERROR

    COMMIT TRAN 
    RETURN 0 


ERR_HANDLER:
PRINT 'Unexpected error occurred!' --RETURN AN ERROR MESSAGE
ROLLBACK TRAN 
RETURN 1

Answer 2

This is of course completely untested but this should give you a nudge in the right direction.

ALTER PROCEDURE [dbo].[ASP_XXNominalBalanceUPDATE]
     @ORGCODE VARCHAR(6),
     @NewNominal INT,
     @OldNominal INT,
     @BalMonth CHAR(6),
     @Amt Decimal(12,2),
     @FINPERIODNR INT,
     @InvDetailID INT

AS 
BEGIN TRAN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)

declare @RowCount1 int
    , @RowCount2 int
    , @RowCount3 int

BEGIN TRANSACTION

BEGIN TRY
    SET @SQL = N'
    UPDATE ccb
    SET ' + @BalMonth + '  = ccb.' + @BalMonth + ' + @AMT '
    + ' FROM CostCodeBalance' + @ORGCODE + ' ccb 
    WHERE ccb.CostCode = @OldNominal AND ccb.FinPeriodNr = @FINPERIODNR'

    EXEC sp_executesql @sql, N'@ORGCODE VARCHAR(6), @OldNominal INT, @BalMonth CHAR(6), @Amt Decimal(12,2), @FINPERIODNR INT', 
                               @ORGCODE, @OldNominal, @BalMonth, @Amt, @FINPERIODNR; 
    set @RowCount1 = @@ROWCOUNT                       


    set @SQL = N'
    UPDATE ccb
    SET ' + @BalMonth + ' = ccb.' + @BalMonth + ' - @Amt'
    + ' FROM CostCodeBalance' + @ORGCODE + ' ccb 
    WHERE ccb.CostCode = @NewNominal AND ccb.FinPeriodNr = @FINPERIODNR'

    EXEC sp_executesql @sql, N'@ORGCODE VARCHAR(6), @OldNominal INT, @BalMonth CHAR(6), @Amt Decimal(12,2), @FINPERIODNR INT', 
                               @ORGCODE, @OldNominal, @BalMonth, @Amt, @FINPERIODNR; 
    set @RowCount2 = @@ROWCOUNT                       

    set @SQL = N'
    UPDATE Posting' + @ORGCODE 
    + 'SET Nominal = @NewNominal
    WHERE Deleted = 0 AND Posting' + @ORGCODE + '.OrderRecNo = @InvDetailID AND OrderType = ''SI'''

    EXEC sp_executesql @sql, N'@ORGCODE VARCHAR(6), @NewNominal INT, @InvDetailID INT', 
                                @ORGCODE, @NewNominal, @InvDetailID; 

    set @RowCount3 = @@ROWCOUNT

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    --Add some logic here for what you want returned when an error occurs.
    ROLLBACK TRANSACTION
END CATCH