Posts
33
Comments
186
Trackbacks
0
Catching Schema Errors in TRY..CATCH block in SQL Server

Recently I was writing a stored procedure in SQL Server 2005. The stored procedure was pretty straight forward with some couple of SQL DML statements inside TRY block and exception being handled and raised in CATCH block.

However, while testing the exception handling of the stored procedure, it didn't seem to go to CATCH block.

All I was trying was - Insert some data into some 'non-existent' table and catch the error in the CATCH block. I was expecting stored procedure to report 'Invalid object name' but it didn't...

BEGIN TRY

SELECT * FROM NonExistentTable

END TRY 

BEGIN CATCH

INSERT INTO EXCEPTION (Number, Message)

VALUES (ERROR_NUMBER(), ERROR_MESSAGE())

END CATCH

Little bit of digging led me to the folowing link

http://www.codeproject.com/KB/database/try_catch.aspx

It is a very good article on Try..Catch statement, according to this the Try/Catch block will not be able to detect any COMPILE errors which includes schema errors.

To be able to CATCH such errors, we should wrap the statements in dynamic SQL in TRY block .. this solved my problem..

BEGIN TRY

DECLARE @sql nvarchar(100)

SET @sql = 'SELECT * FROM NonExistentTable'

EXEC sp_executesql @sql

END TRY

BEGIN CATCH

INSERT INTO EXCEPTION (Number, Message)

VALUES (ERROR_NUMBER(), ERROR_MESSAGE())

END CATCH

Hope this helps someone too!

 

posted on Wednesday, June 8, 2011 3:15 PM Print
Comments
Gravatar
# re: Catching Schema Errors in TRY..CATCH block in SQL Server
shathar khan
4/4/2012 11:17 AM
hey! execute the procedure inside another

try catch block

Post Comment

Title *
Name *
Email
Comment *  
Verification