TRY/CATCH blocks in SQL

Question

Post reply

TRY/CATCH blocks in SQL

richardmgreen1

SSChampion

Points: 10302
More actions
January 29, 2016 at 7:35 am

#399643

Hi all
We've got a lot of stored procedures in source control and, currently, we use an "IF EXISTS" construct to drop the procedure and recreate it any time changes are made.
I'm looking to use a TRY/CATCH block to do the same thing (and hopefully cut down on typing) but I can't get it to work.
My current error is "Incorrect syntax near the keyword 'PROCEDURE'." against all the procs I've tried it on.
Has anyone got any ideas why this can't be done or am I missing something obvious in order to make it work?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

You cant use a Create <object> inside a try catch scenario as the CREATE needs to be the first statement.

That's why you use the If Exists.

If you want to cut down on typing look at using a DB project in VS studio shell, and use the Schema compare feature.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

There are some objects that can be created inside a TRY/CATCH block, such as tables and indexes. Some objects, such as procedures should be the only thing in the batch. Here are some other limitations for stored procedures: https://msdn.microsoft.com/en-us/library/ms187926.aspx#Anchor_5

Out of curiosity, what would you intend to do with the TRY/CATCH? What code would you include in the CATCH to handle the error?

If you want to save some keystrokes, you can avoid IF EXISTS, using just the value of OBJECT_ID()

IF OBJECT_ID(N'[dbo].ProcedureName', N'P') IS NOT NULL

DROP PROCEDURE ProcedureName

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

richardmgreen1 SSChampion Points: 10302 More actions · Answer 3

Thanks for the replies folks.

Luis - Inside the TRY block would be the DROP statement. Inside the CATCH block would be the CREATE statement.

Thinking about this logically, we'd need have the create statement in the TRY block as well (or we'd have to run the proc twice).

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

Please up-vote if you agree with the suggestion: CREATE OR ALTER statement

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

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 5

It's not a very good practice to drop and re-create procedures. You may want to use this approach instead:

IF OBJECT_ID ('dbo.ProcedureName', 'P') IS NULL

BEGIN

EXECUTE ('CREATE PROCEDURE dbo.ProcedureName AS SELECT 1')

END

GO

ALTER PROCEDURE dbo.ProcedureName

...

Alex Suprun

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

Alexander Suprun (1/29/2016)
It's not a very good practice to drop and re-create procedures.

Curious on your resaoning, why do you say so?

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

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 7

Orlando Colamatteo (1/29/2016)
Alexander Suprun (1/29/2016)
It's not a very good practice to drop and re-create procedures.
Curious on your resaoning, why do you say so?

Because when you drop procedure you also drop all the permissions.

Alex Suprun

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

Alexander Suprun (1/29/2016)
Orlando Colamatteo (1/29/2016)
Alexander Suprun (1/29/2016)
It's not a very good practice to drop and re-create procedures.
Curious on your resaoning, why do you say so?
Because when you drop procedure you also drop all the permissions.

I wouldn't call it a poor practice to ALTER instead of DROP/CREATE. There are merits to both approaches. Depending on the environment dropping and recreating is preferred. Putting the GRANTs at the bottom of a script that contains an IF EXISTS...DROP PROC followed by the proc definition could comprise a standard script template. From a deployment standpoint it works well because whether it was the first time the proc went to an environment (e.g. QA or PROD) or the n-th time the script was deployed the script works. This is not to mention that the permissions are self-contained in the script making it simple for developers to check out a script, develop on it and recompile as needed without worry they need to run anything additional. Of course, your mileage may vary.

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 9

I saw a great presentation on temp tables and table variables that explored, among other things, the different approaches to creating temp tables. it punched holes in many of the different approaches. The safe one was determined to be the one that Luis posted:

Luis Cazares (1/29/2016)
IF OBJECT_ID('tempdb.dbo.#tablename, 'u') IS NOT NULL DROP TABLE #tablename;

I use the same approach to create new stored procedures and functions in the initial release script. After they're created and in production, I alter them with ALTER statements. Either way, the procedure cache is invalidated, so it has to be recreated during the first run.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions