SQL TRY/CATCH testing for existence.

Mr_Shaw
Mr_Shaw used Ask the Experts™
on
I am reading Microsoft SQL Server 2008 - Database Development book for example 70-433.

On page 155 (in the NOTE TRAPPING ERRORS) it states that "you cannot use TRY...CATCH block to test for an objects existence".

I put this to the test and the TRY did pass control to the CATCH. Here is my code. Can anybody explain this to me...Thanks.

CREATE TABLE dbo.test
(
ID INT NOT NULL PRIMARY KEY
)

BEGIN TRY
CREATE TABLE dbo.test
(
ID INT NOT NULL PRIMARY KEY
)
END TRY
BEGIN CATCH
PRINT 'Table cannot be created'
END CATCH
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Data Architect
Commented:
You're testing if an object can be created in your example, and the TRY...CATCH would handle that properly. They're referring to something liek this:

BEGIN TRY
   select * from SomeTableThatDoesntExist
END TRY
BEGIN CATCH
    PRINT 'Table does not exist'
END CATCH

Open in new window


If you execute that, you'll get a SQL Error, not your printed error statement from the CATCH block. What they're saying is that a missing object won't pass control to the CATCH block, it will stop your script in its tracks, so you can't use TRY...CATCH for control flow in this case.

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial