test

Question

test

sql_learner29

SSCrazy Eights

Points: 9107
More actions
March 18, 2011 at 9:35 am

#233359

USE AdventureWorks2008R2;
GO
BEGIN TRY
-- Table does not exist; object name resolution
-- error not caught.
SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
RETURN
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

You posted this to a SQL 2005 forum, but the code uses 2008R2. You may find better success if you post to the proper forum; it also keeps things clean and tidy around here.

Also, are we supposed to infer a question from this pretty code, or are you just posting an informative code block and /*comment*/ for us?

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

The code does not even compile. It is missing a SELECT in the CATCH block to deliver the error details.

I think the poster was trying to point out that the code will not cause an error when compiled into a stored procedure because of deferred name resolution...where's the question?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 3

I was just confused because the paragraph in the help that is just above his copy/pasted code (except that he replaced the SELECT in the Catch with RETURN) explains this error:

http://msdn.microsoft.com/en-us/library/ms179296.aspx

"Unlike the syntax error in the previous example, an error that occurs during statement-level recompilation will not prevent the batch from compiling, but it will terminate the batch as soon as recompilation for the statement fails. For example, if a batch has two statements and the second statement references a table that does not exist, deferred name resolution causes the batch to compile successfully and start execution without binding the missing table to the query plan until that statement is recompiled. The batch stops running when it gets to the statement that references the missing table and returns an error. This type of error will not be handled by a TRY…CATCH construct at the same level of execution at which the error occurred. The following example demonstrates this behavior."

So I was a bit confused also. That's all.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph