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.
| Author |
Topic |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
 Posted - 2011-06-23 : 11:17:25
|
| I notice that, in Studio, if I want to drop and create a proc, the IDE inserts the statements:IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[proc]GOI get that, but I see that this also works fine:BEGIN TRY DROP PROC [dbo].[proc]END TRYBEGIN CATCHEND CATCHWhich basically just tries to drop the proc but throws away any error, including not found. I realize that doing it this way might catch other errors that go unreported with the DROP, but I'm thinking that, whatever those errors might be, they will be thrown by other operations as well, since they're likely to be rather serious. (Why else would DROP fail, if the proc is really there and I have permission to drop it?)Anyway, I'm looking for feedback on using BEGIN TRY this way. Pros, Cons, Gotcha's etc. FWIW I like it since I don't need that long IF statement and I don't have to repeat the proc's name (once in the IF and once in the DROP) to drop it. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-23 : 11:51:43
|
| You may need a the keyword PROCEDURE as in "DROP PROCEDURE [dbo].[proc]"That aside, using try-catch to handle this is not a good idea - exception handling is really meant for handling EXCEPTIONS. However, I can be persuaded otherwise. But, more than that, a silent catch is a very bad idea - not a recommended practice. Don't do it, especially when there is a better way, which you have included in your posting.Usual disclaimers about this being my humble opinion applies. |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2011-06-23 : 12:11:12
|
quote:
Originally posted by sunitabeck
You may need a the keyword PROCEDURE as in "DROP PROCEDURE [dbo].[proc]"That aside, using try-catch to handle this is not a good idea - exception handling is really meant for handling EXCEPTIONS. However, I can be persuaded otherwise. But, more than that, a silent catch is a very bad idea - not a recommended practice. Don't do it, especially when there is a better way, which you have included in your posting.Usual disclaimers about this being my humble opinion applies.
Thanks for catching my error! I fixed it. FWIW I'm not sure the standard way is better. It's just more verbose. For me, simpler is better, if you get the same results without nasty side effects. Hence my question. So, rephrasing, what other exceptions might DROP PROC throw -- that is other than a not-found error or permissions problem.Put another way: You can program with LBYL (Look Before You Leap) approach, which is the standard way in this case, or with EAFP (Easier to Ask Forgiveness than Permission). I prefer EAFP since it makes the code simpler for the expected case. Using TRY/CATCH is classic EAFP, while the IF/THEN is LBYL. I assume others here prefer LBYL, and would like to learn from more experienced SQLers |
|
|
|
|
|
|
|
|
Dropping a proc before re-creating it