Question about stored procedures - please advise.
in [Programming SQL]
|
From: Mitchell_Collen via SQLMonster.com on 8 Jun 2010 10:42 Hello SQL Monsters If a stored procedure exists as complied code on my server then why would you drop a procedure and then recreate it? Doesn't this delete the execution plan? If so, is there a programmable way to create a procedure only if one is not found otherwise alter the procedure? Thanks in advance, MC snippet: IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'myStoredProcedure') DROP PROCEDURE dbo.myStoredProcedure GO create myStoredProcedure... -- Misty :-) Message posted via http://www.sqlmonster.com
From: Bob Barrows on 8 Jun 2010 11:06 Mitchell_Collen via SQLMonster.com wrote: > Hello SQL Monsters > > If a stored procedure exists as complied code on my server then why > would you drop a procedure and then recreate it? Err ... so you can modify the code in the procedure? In earlier versions, there was no ALTER PROCEDURE command so that was the only way to modify the procedure. > Doesn't this delete the execution plan? Well, yes ... given that you are modifying the code, you want a new execution plan, don't you? The real problem with this template is that dropping the procedure also drops any permissions that were granted for that procedure, so your script also has to include code to grant any needed explicit permissions. > If so, is there a programmable way to create a > procedure only if one is not found otherwise alter the procedure? > Thanks in advance, MC Sure, but that means having a script with both the CREATE PROCEDURE and ALTER PROCEDURE batches in it, meaning that one would need to maintain both batches when modifying the procedure. > > snippet: > IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES > WHERE SPECIFIC_SCHEMA = N'dbo' > AND SPECIFIC_NAME = N'myStoredProcedure') > DROP PROCEDURE dbo.myStoredProcedure > > GO > > create myStoredProcedure... I've seen a template in use that creates a "placeholder" procedure consisting of a single line of code if the procedure does not exist, and then uses ALTER PROCEDURE to alter the code in that procedure. But you do realize that ALTER also forces a recompile and new execution plan, so I'm not certain what you think you are gaining as far as your "recompilation" question is concerned. -- Bob Barrows
From: --CELKO-- on 8 Jun 2010 11:09 I think what you are trying to find in SQL Server is the "CREATE OR REPLACE PROCEDURE.." statement in Oracle.
From: Tom Cooper on 8 Jun 2010 12:07 It is true that when you use a stored proc, it is compiled into an execution plan. But this is only in memory, and will go away for many reasons, including dropping and recreating the stored proc, but also ALTERing the stored proc, or changing the schema of any of the tables the stored proc uses, or just that SQL needs the memory used by the execution plan, so it gets rid of the plan and then will recreate it the next time the stored proc is used and there are other reasons a new execution plan is created. There are rare exceptions, but generally creating an execution plan for a stored proc is quick and inexpensive. Now if you are recompiling the plan thousands of times a day, those cheap costs can add up to a major expense. But since you are only going to change a stored proc in production very rarely, the cost is so small you don't have to worry about it when you are changeing a stored proc. The major advantage of DROPping (if it already exists) and CREATEing a stored proc is that it works whether or not the stored proc already exists. The advantage of ALTERing a stored proc is it preserves any permissions you have defined on the stored proc. Tom "Mitchell_Collen via SQLMonster.com" <u33726(a)uwe> wrote in message news:a9393105f550d(a)uwe... > Hello SQL Monsters > > If a stored procedure exists as complied code on my server then why would > you > drop a procedure and then recreate it? Doesn't this delete the execution > plan? > If so, is there a programmable way to create a procedure only if one is > not > found otherwise alter the procedure? > Thanks in advance, MC > > snippet: > IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES > WHERE SPECIFIC_SCHEMA = N'dbo' > AND SPECIFIC_NAME = N'myStoredProcedure') > DROP PROCEDURE dbo.myStoredProcedure > > GO > > create myStoredProcedure... > > -- > Misty :-) > > Message posted via http://www.sqlmonster.com >
From: Sylvain Lafontaine on 8 Jun 2010 13:38
As other people has said, you cannot create a SP is one already exists or alter it if it doesn't exists; so people will often check first for its existence and if it's there, delete it before using a Create Procedure statement. However, this also has the disadvantage of deleting all permissions associated with it; so you have to recreate those permissions as well. A second possibility - that I never used myself - would be to do the reverse; ie., to create a procedure if it doesn't already exists followed by an Alter statement; something like: IF Not EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'myStoredProcedure') Exec sp_ExecuteSQL N'Create PROCEDURE dbo.myStoredProcedure as' GO Alter Procedure dbo.myStoredProcedure as begin Select 1 as Number; End Go Exec dbo.myStoredProcedure We need to use a call to sp_ExecuteSQL here because a Create Procedure must be in its own batch and therefore, cannot be part of an IF statement. Also, I've used what looks like to be as the shortest Create Procedure that I could find: N'Create PROCEDURE dbo.myStoredProcedure as' but you could use something more liberal like N'Create PROCEDURE dbo.myStoredProcedure as return 0;' Again, I never used something like this myself; so I don't know about any counter-indication at this moment. -- Sylvain Lafontaine, ing. MVP - Access Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Mitchell_Collen via SQLMonster.com" <u33726(a)uwe> wrote in message news:a9393105f550d(a)uwe... > Hello SQL Monsters > > If a stored procedure exists as complied code on my server then why would > you > drop a procedure and then recreate it? Doesn't this delete the execution > plan? > If so, is there a programmable way to create a procedure only if one is > not > found otherwise alter the procedure? > Thanks in advance, MC > > snippet: > IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES > WHERE SPECIFIC_SCHEMA = N'dbo' > AND SPECIFIC_NAME = N'myStoredProcedure') > DROP PROCEDURE dbo.myStoredProcedure > > GO > > create myStoredProcedure... > > -- > Misty :-) > > Message posted via http://www.sqlmonster.com > |