CODEWITHSUNDEEP
sql-serversql-server-2008
Sandeep Kumar
Sandeep Kumar

Reputation: 803

Why the procedure is not going into the catch block

I have a stored procedure which is selecting the entry from the Entity table which doesn't exists in our database.

In the code below if I execute the stored procedure, it's not going into catch block while for every error in try block it should go to the catch block automatically.

I am not able to understand the reason

Create PROCEDURE AddUpdateEntity
   (@Name VARCHAR(20),
    @Age SMALLINT)
AS
  BEGIN TRY
     SELECT NAME, Age FROM Entity WHERE NAME = @name AND Age = @Age
  END TRY
  BEGIN CATCH
     SELECT ERROR_NUMBER() StatusCode, ERROR_MESSAGE() [Message]
  END CATCH
GO

/* Command to execute the SP */
EXEC AddUpdateEntity 'Sandeep',20

This execute statement is showing the error "Invalid Entity Object" but not calling the catch block.

The stored procedure has crashed and is showing the message

Msg 208, Level 16, State 1, Procedure AddUpdateEntity, Line 10
Nom d'objet 'Entity' non valide.

Upvotes: 1

Views: 2893

Answers (2)

Jyoti Kumar
Jyoti Kumar

Reputation: 116

As per the MSDN (follow link http://msdn.microsoft.com/en-us/library/ms175976.aspx)

Errors Unaffected by a TRY…CATCH Construct

TRY…CATCH constructs do not trap the following conditions:

  • Warnings or informational messages that have a severity of 10 or lower.

  • Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.

  • Attentions, such as client-interrupt requests or broken client connections.

  • When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  • Compile errors, such as syntax errors, that prevent a batch from running.

  • Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

These errors are returned to the level that ran the batch, stored procedure, or trigger.

If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY…CATCH construct and will be handled by the associated CATCH block.

Hope the above description will solve your problem.

Upvotes: 9

p.s.w.g
p.s.w.g

Reputation: 149020

It will never enter the CATCH block simply because no rows are returned from a query. A sql query which returns no rows isn't an error; it's expected behavior.

As others have pointed out, the error you're seeing appears to be a compile-time error, because the Entity table does not exist.

You could try using dynamic sql with sp_executesql to force the SP to check for the tables existence only at run-time.

BEGIN TRY
    DECLARE @sqlStr NVARCHAR(4000),
            @sqlParams NVARCHAR(400);
    SET @sqlStr = N'SELECT Name, Age FROM Entity WHERE Name=@Name AND Age=@Age';
    SET @sqlParams = '@Name VARCHAR(20), @Age SMALLINT';
    sp_executesql @sqlStr, @sqlParams, @Name = @Name, @Age = @Age
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() StatusCode, ERROR_MESSAGE() [Message]
END CATCH

Upvotes: 1

Related Questions