Home » SQL Server Try Catch with Nested Queries

SQL Server Try Catch with Nested Queries

SQL Server has the Error handling capabilities by using Try Catch which not only handling the errors but gives Error description useful in debugging the Code.
The Try Catch can also be used with Nested Queries but will need to be careful in understanding the Errors raised when the Error occuring in a Nested Query/Stored Procedure.

Now let me explain how I use Try Catch in the Codes and how nested query also raise the Error properly.

Let us begin with developing a SP called TryCatchDemo.

alter PROCEDURE [dbo].[TryCatchDemo]
AS

BEGIN TRY
DECLARE
--mark the transaction as started if @@Trancount variable is Zero
@TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
--For understanding the functionality we will use select statements in between each block which gives us idea as to which blocks are executed in the Stored Procedure when executed.
select 1 as "one";
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0

select 2 as "two";
exec dba.dbo.test

IF( @TranStarted = 1 )
BEGIN
select 3 as "three";
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
END TRY
BEGIN CATCH
select 4 as "four";
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER();

IF( @TranStarted = 1 )
BEGIN
select 5 as "five";
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH


The Nested Stored Procedure used is dba.dbo.test which is given below:

alter procedure test as
select 123 as "inner";
--Change the below "Employee" table to any table which exists on Development server
select * from Employee;

Now without any errors if we execute [TryCatchDemo] it succeeds.

If we introduce an error in “Test” Stored Procedure by changing “Employee” table to “L_Employee1” where “L_Employee1” table does not exist on development server then we should get error and results as below:

Error Message is shown below:

In Messages you can see the blocks touched by the execution of stored Procedure.

The blocks touch are 1, 2, 123(inner query 1 in nested SP), 4, 5.

Hope this helps. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *