Bugs in TRY..CATCH block??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bugs in TRY..CATCH block???

Author

Topic

aex
Yak Posting Veteran

60 Posts

Posted - 2008-10-15 : 00:05:58

Over the time, I have used TRY..CATCH block as for granted without caring much how it really works, as I expect that the feature would work the same way as in .NET language such as C#.

Today I find one error in my stored procedure, which the error is not caught. I am wondering why it happens as I have surrounded my T-SQL statement in TRY..CATCH block. It takes me quite a long time to troubleshoot and I end up discovering a very ridiculous fact about T-SQL TRY..CATCH block.

To reproduce the 'bug?' in simple way, lets assume that I declare a table as below:

CREATE TABLE MyTable ( StaffId INT )

The next thing to do is to query the table by querying the invalid column name:

BEGIN TRY
SELECT NonExistField FROM MyTable
END TRY
BEGIN CATCH
DECLARE @ErrorNum INT,
@ErrorMsg NVARCHAR(4000)

SELECT @ErrorNum, @ErrorMsg
END CATCH

If the code above is executed, I find that the error is not caught in the CATCH block as I do not see result of 'SELECT @ErrorNum, @ErrorMsg' statement is returned.

Do any one realize the above flaw, is it a known issue for sql server? Or there is something that I have missed?

aex

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2008-10-15 : 00:58:29

This is by design. See books online for try catch

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.

aex
Yak Posting Veteran

60 Posts

Posted - 2008-10-15 : 01:44:23

Thanks cr8nk for reminding me that.

But still I prefer SQL server would complain to me during compile time whenever any of my SELECT, UPDATE, DELETE clause that is run against non-existence table, fields, etc.

Anyhow, like what you've suggested, that is by design.

Ya, thanks again for the tips, and it really helps.

aex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources