WHY it is not catching error?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

WHY it is not catching error??

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-08 : 03:50:48

I am trying to do so...

ALTER PROC TestError
AS

BEGIN TRY
SELECT * FROM NonexistentTable
END TRY

BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH

In this NonexistentTable table is not present in database, so i suppose to get the output like..

Error Message
Invalid object name 'NonexistentTable'.

But it is not throwing this error. Infact error is..

Msg 208, Level 16, State 1, Procedure TestError, Line 5
Invalid object name 'NonexistentTable'.

Why this is happening ???

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-08 : 04:11:02

Because the procedure is validated before it is saved to the database and it doesn't validate. If you want to force the catch-block to run do something like this:


declare @sql nvarchar(200)
begin try
  set @sql = 'select top 1 * from nonexistanttable'
  exec sp_executesql @sql
end try

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-08 : 07:38:36

Because you can't catch syntax or binding errors with try-catch.

--
Gail Shaw
SQL Server MVP

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-08 : 08:43:15

Ok fair enough.
Thanks for kind replies.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources