CODEWITHSUNDEEP
sqlsql-servert-sql
w0051977
w0051977

Reputation: 15817

Anticipating fatal errors when using stored procedures

Please see the code below:

ALTER PROCEDURE GetPerson

AS
BEGIN TRANSACTION;
BEGIN TRY
    DECLARE @TestVariable1 AS INT;
    DECLARE @TestVariable2 AS INT;
    SET @TestVariable1 = 1;
    SET @TestVariable2 = 0;
    DECLARE @TestVariable3 AS INT;
    SET @TestVariable3 = @TestVariable1 / @TestVariable2;
    PRINT 'transaction committed';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'transaction rolled back';
END CATCH

I could run this using the command: 

EXEC GetPerson

The transaction is successfully rolled back because you cannot divide my zero.

Now see the code below:

ALTER PROCEDURE GetPerson

AS
BEGIN TRANSACTION;
BEGIN TRY
    CREATE TABLE #Test
    (
        test INT
    );
    SELECT TOP 1 *
    FROM   #Test;
    SELECT TOP 1 *
    FROM   person.person;
    DROP TABLE #Test;
    SELECT TOP 1 *
    FROM   #Test;
    PRINT 'transaction committed';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'transaction rolled back';
END CATCH

If I run the same comment: 

EXEC GetPerson

then I get an error:

Msg 208, Level 16, State 0, Procedure GetPerson, Line 11
Invalid object name '#Test'.

Msg 266, Level 16, State 2, Procedure GetPerson, Line 11
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1

I understand that not all errors are caught by the TRY clause as explained here: https://msdn.microsoft.com/en-gb/library/ms178592.aspx

When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block

My question is: how do you deal with these? I know in my code it is a clear developer mistake to try to select from a temp table that no longer exists. However, I am wandering if there are other errors I should deal with.

Is it acceptable practice to do this when calling the stored procedure:

EXECUTE GetPerson ;

IF @@trancount >= 1
ROLLBACK;

Alternatively I could do something like this:

EXEC GetPerson
    IF @@trancount >= 1
    INSERT INTO dbLog ("Trancount greater than 1 after running GetPerson")

Upvotes: 0

Views: 630

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

Error 208 is a compilation error. Your code never run, anything, because it could not even compile. This is the same as asking a C# program to catch a compile error.

Besides, if you want a correct try/catch block that handles exceptions and transaction, see Exception handling and nested transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

Obviously this will not handle syntax/binding errors (like 208), but at least will handle correct procedures. Remember that checking XACT_STATE() in the CATCH block is mandatory. Consider an error like 1205 (deadlock) where the CATCH block runs after the transaction was rolled back. You also need to honor and handle caller's transaction.

Upvotes: 3

Related Questions