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!