SQL Server 2005 error handling gotcha
One of the cool features introduced in SQL Server 2005 was the improved error handling capabilities in Transact-SQL through the try/catch construct. I’m not going to talk about it here since there is plenty of material on the subject on the web, I just want to point out a gotcha I found a few days ago at work.
Take the following stored procedure:
CREATE PROCEDURE MyProcedure
AS
SELECT x FROM Person.Address
SELECT y FROM Person.Contact
If you try to create the procedure in the AdventureWorks database it will fail because the x and y columns don’t exist:
Msg 207, Level 16, State 1, Procedure MyProcedure, Line 3
Invalid column name ‘x’.
Msg 207, Level 16, State 1, Procedure MyProcedure, Line 4
Invalid column name ‘y’.
If you execute the same query as dynamic SQL (through the sp_executesql stored procedure) you get the same 2 errors:
DECLARE @Command nvarchar(2000)
SET @Command = ‘CREATE PROCEDURE MyProcedure
AS
SELECT x FROM Person.Address
SELECT y FROM Person.Contact’
EXEC sp_executesql @Command
Now let’s use a try/catch construct to handle the error:
DECLARE @Command nvarchar(2000)
SET @Command = ‘CREATE PROCEDURE MyProcedure
AS
SELECT x FROM Person.Address
SELECT y FROM Person.Contact’
BEGIN TRY
EXEC sp_executesql @Command
PRINT ‘Procedure MyProcedure created.’
END TRY
BEGIN CATCH
PRINT ‘Procedure MyProcedure couldn”t be created: ‘ + ERROR_MESSAGE()
END CATCH
This time the ERROR_MESSAGE function only returns the first error message:
Procedure MyProcedure couldn’t be created:Invalid column name ‘x’.
It looks you can only get information about the first error hat occurred in a try block. I believe the reason is that control is immediately transferred to the catch block after the first error occurs because when you execute the naked sp_execute procedure it shows the 2 errors.
Most of the time this is not an issue, unless you want to execute DDL statements (since SQL Server may report several errors). This was the case for me, I was working on a script that drops all the database objects and creates them again (to check if any of them got broken by some changes in the schema), and I wanted to report all the errors, but I had to give up and just report the objects that had errors.
If I discover a way to get around this limitation I’ll post about it, but I doubt there’s one. Over and out!