Solved

SQL TRY/CATCH testing for existence.

Posted on 2011-09-02
2
225 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Mr_Shaw
2 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 36473615
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.
0
 

Author Closing Comment

by:Mr_Shaw
ID: 36473645
thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question