SQL Try Catch in stored procedure - am I doing it right?

Question

I have a stored procedure that performs a number of inserts, each insert will return a SCOPE_IDENTITY() primary key value which is then used in as subsequent insert's foreign key.

To prevent a situation where an insert is performed without a FK because the previous insert has failed, I've wrapped the inserts in try-catch blocks like this:

DECLARE @error INT;

BEGIN TRY
    insert statement here...
    SET @newId = SCOPE_IDENTITY();
END TRY
BEGIN CATCH
    -- assign error value to 
    SET @error = @@ERROR;
END CATCH

Then the next insert checks the error var before continuing:

IF @error = 0
BEGIN
    BEGIN TRY
        next insert statement here using `@newId` from previous qry...
        SET @nextId = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        // assign error value to 
        SET @error = @@ERROR;
    END CATCH
END

And so on. Is this a suitable application of Try-catch or is it overkill?

Answer 1

That will work fine, but if your intent here is to simply not do any more work in the procedure after the first failure (i.e. within the catch of a wrapped insert), then you could either:

• Use only a single try/catch block (and flow would skip from the first failing insert statement to the catch block, skipping all the following inserts that depend on the previous one(s))
• If you prefer to use multiple try/catches for whatever reason, you could simply either re-throw the exception from the catch block (only valid/supported on SQL Server 2014 and later) or simply return from the catch block if running pre-Sql Server 2014 (after you've done any cleanup you wish to do).

In 2014 and later, using THROW from within the catch block will re-throw the exception and exit the current statement batch immediately (see here for details, if using in a stored procedure for example, this basically means it will exit from the current procedure).

As a simple example using the THROW in the catch block (2014+):

if object_id('tempdb..#TestRethrow') is not null
    drop table #TestRethrow;

CREATE TABLE #TestRethrow(ID INT PRIMARY KEY);

BEGIN TRY
    INSERT #TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
    PRINT 'In catch block 1 - SHOULD NOT FIRE.';
    THROW;
END CATCH;

print 'between catch blocks';

BEGIN TRY
    -- Unique key violation
    INSERT #TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
    PRINT 'In catch block 2 - WILL FIRE';
    THROW;
    PRINT 'At end of catch block 2 - SHOULD NOT FIRE';
END CATCH;

print 'out of catch block 2 - SHOULD NOT FIRE'
select 1;

go

print 'SHOULD FIRE - Another batch (assuming you are using SSMS for example, or another client that separates batches using GO';
go

If using SQL 2012 or earlier, you could use a return in place of the throw, but you'd likely want to do something with the exception first (i.e. log it, or raise it using the older RAISERROR syntax):

    if object_id('tempdb..#TestRethrow') is not null
    drop table #TestRethrow;

CREATE TABLE #TestRethrow(ID INT PRIMARY KEY);

BEGIN TRY
    INSERT #TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
    PRINT 'In catch block 1 - SHOULD NOT FIRE.';
    return;
END CATCH;

print 'between catch blocks';

BEGIN TRY
    -- Unique key violation
    INSERT #TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
    PRINT 'In catch block 2 - WILL FIRE';
    return
    PRINT 'At end of catch block 2 - SHOULD NOT FIRE';
END CATCH;

print 'out of catch block 2 - SHOULD NOT FIRE'
select 1;

go

print 'SHOULD FIRE - Another batch (assuming you are using SSMS for example, or another client that separates batches using GO';
go

Answer 2

From Here

A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.

That means a nested Try...Catch block does not effect the outer Try..Catch block code if Error Occurs in the inner Block.

Here you want to us the result of One Query To Another,So, You Better use single Try..Catch Block

Cause

If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has finished running, control passes to the statement immediately after the associated END CATCH statement. If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger.

So, In this case no extra execution fear after First Error Occurrence.

Example:

Using Nested Try...Catch

BEGIN try
  declare @param1 as int
    BEGIN Try
        set @param1='gkjk'
        select @param1
     END TRY
       BEGIN CATCH
         SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;  
        END CATCH
    BEGIN TRY
        select 'hello','i m in',200
    END TRY
    BEGIN CATCH
      SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;  
    END CATCH
END TRY
  BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
  END CATCH

It'll Give You Two Result Set.

Using Single Try...Catch

BEGIN TRY
    declare @param1 as int
     set @param1='gkjk'
     select @param1
     select 'hello','i m in',200
END TRY

BEGIN CATCH
SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;  

 END CATCH

Where as This Will Give you Single Result.

Answer 3

It's not overkill if you intend to handle the errors returned by the catch. An insert can't technically occur without a foreign key so you're not preventing invalid data with try/catch. So, without the try/catch, your second insert would still fail if the first failed.

To take this approach even further, you might consider doing your inserts in transaction, with commit and rollback. See the section on un-committable transactions in this TechNet article.