June 11, 2008 at 9:01 am
Many stored procedures begin the first few lines with:
IF EXISTS ...
DROP PROCEDURE ...
They don't all start that way but many do. Why include the command to drop and recreate the procedure within the stored procedure? If one is deploying a new version of the stored proc, I can completely understand why they would drop it first and then recreate it. But it seems terribly inefficient to blow away the stored procedure and recreate the thing every time it runs. Is it a good practice to include the DROP PROCEDURE step in all of my stored procedures and why?
I know this probably sounds like a total noob question but I have actually been working with SQL Server for many years and I have encountered this many times. I've just never tried to understand why many stored procs start this way. Now that I am beginning to write stored procs regularly, I really want to understand why they are structured in this way and more importantly, I want to understand when it is necessary to include the IF EXISTS…DROP PROCEDURE clause.
Thanks in advance.
Jim
June 11, 2008 at 9:08 am
It's somewhat legacy, and it's good practice since in a DR situation, you need the CREATE. If you redeploy permissions, you're ok. If not, this is bad and you need to reapply permissions and you should instead move to an ALTER syntax.
June 11, 2008 at 9:30 am
We have to do deployments out of source control. Sometimes they are incremental deployments and we could get away with having an ALTER statement instead of a DROP & CREATE, but if we just had a CREATE with no DROP it would fail. Sometimes the deployments are complete tear down & build where we replace the entire data structure. There, we could just have CREATE statements, but if we had ALTER statements they would fail. The CREATE & DROP just seems to cover more contengencies in an efficient way.
Of course, when you move to Visual Studio Team Edition for Databases, then you only see the CREATE statement, but the tool generates ALTER if needed by itself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2008 at 12:31 pm
There are some reasons to drop and create, namely a time stamp on when it was created. Useful especially in environments that might have a less than perfect move procedure in place to track down what was changed last.
You do have to pay attention to get the security set up each time though. Yes I have been bitten by this a "fw" times in the past.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply