Prev: Begin and End Block in an IF statement
Next: Cast Varchar to Decimal?
From: Sylvain Lafontaine on 8 Jun 2010 14:50
Well, using � Exec ('Create PROCEDURE dbo.myStoredProcedure as') � will be a
little simpler than using a call to sp_ExecuteSQL as in with � Exec
sp_ExecuteSQL N'Create PROCEDURE dbo.myStoredProcedure as' �.

--
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message
news:egw37FzBLHA.4388(a)TK2MSFTNGP04.phx.gbl...
> 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
>>
>
>


From: Erland Sommarskog on 8 Jun 2010 18:02
Mitchell_Collen via SQLMonster.com (u33726(a)uwe) writes:
> 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...

A pattern that I've sometimes used is:

IF NOT EXISTS (object_id('my_sp') IS NULL)
EXEC('CREATE PROCEDURE my_sp AS PRINT 12')
go
ALTER PROCEDURE my_sp @par1 int ... AS

In this way ALTER PROCEDURE always works.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2
Prev: Begin and End Block in an IF statement
Next: Cast Varchar to Decimal?