SQL Server Performance

Try / catch while calling secondary stored procedure

Discussion in 'ALL SQL SERVER QUESTIONS' started by darkangelBDF, Aug 16, 2012.

  1. darkangelBDF Member

    Hi there. I have a stored procedure that collects data modified in the past day. This is put into a temp table. Then a while (1=1) loop is initiated where the top 1 record is selected from the temp table into a temp table variable. Several other variables are initiated from this temp table variable. At this point a BEGIN TRY is started with a BEGIN TRANSACTION following. The variables initiated are used as input parameters for another stored procedure called within this transaction. Should an error be created by the secondary stored procedure, this error then needs to inserted into a table created for this purpose, and the rest of the records be processed until done. It doesn't seem to be working. Can someone spot anything I might be doing wrong? It tells me about "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements." It looks something like:

    CREATE [storedProcedure]
    AS
    BEGIN TRY
    DECLARE @variables

    DROP #tempTable if it exists

    CREATE #tempTable

    DECLARE @tempTableVar
    INSERT INTO #tempTable

    WHILE (1=1)
    BEGIN
    INSERT INTO @tempTableVar
    SELECT TOP 1 ... FROM #tempTable WHERE Is Processed = 0

    SELECT @variables = variable FROM @tempTableVar --All necessary variables

    BEGIN TRY
    BEGIN TRANSACTION

    Check to see if there is a record, if not break and raise error.

    EXEC secondaryStoredProcedure @variables

    UPDATE #tempTable
    SET IsProcessed = 1
    WHERE <conditions are met>

    DELETE FROM @tempTableVar

    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH

    IF @@TRANCOUNT > 0
    ROLLBACK TRAN;

    SELECT
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorLine = ERROR_LINE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    SELECT @ErrorMessage = ERROR_MESSAGE() + '(Error %d, Level %d, State %d, Procedure %s, Line %d).'

    INSERT INTO [Marketing].[ImportErrors] (
    ErrorType, --nvarchar(50)
    ErrorMessage --nvarchar(max)
    ) VALUES (
    'Accounts',
    @ErrorMessage);

    END CATCH

    END
    END TRY

    BEGIN CATCH

    IF (XACT_STATE() <> 0)
    --IF @@TRANCOUNT > 0
    ROLLBACK TRAN;

    -- Do the error handling.
    -- Assign variables to error-handling functions that
    -- capture information for RAISERROR.
    SELECT
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorLine = ERROR_LINE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Building the message string that will contain original
    -- error information.
    SELECT @ErrorMessage =
    'Message: '+ ERROR_MESSAGE() + N' (Error %d, Level %d, State %d, Procedure %s, Line %d).';

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR (
    @ErrorMessage,
    @ErrorSeverity,
    1,
    @ErrorNumber, -- parameter: original error number.
    @ErrorSeverity, -- parameter: original error severity.
    @ErrorState, -- parameter: original error state.
    @ErrorProcedure, -- parameter: original error procedure name.
    @ErrorLine); -- parameter: original error line number.

    END CATCH

Share This Page