Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
---
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
----
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
---
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
---
Thanks for your help
PatrickPFI wrote:
> the procedure exists...
> As CREATE PROCEDURE can not be combined with any other Transact-SQL
> statement in a single batch, I was wondering if there were any way to
> achieve something like this :
> (..)
Unfortunately there is no 'CREATE OR REPLACE' statement in SQL Server.
I think your idea to create a procedure if it doesn't exist and then
alter it to desired form (instead of dropping and creating it) is quite
reasonable.
Of course your script has to be modified. I suggest you use something
like this:
IF OBJECT_ID('Procedure1') IS NULL
EXEC ('CREATE PROCEDURE Procedure1 AS SELECT 1')
GO
ALTER PROCEDURE Procedure1
AS
BEGIN
SELECT 2
RETURN 0
-- (..)
END
Best regards,
Marcin Guzowski
http://guzowski.info|||On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> > theprocedureexists...
> > AsCREATEPROCEDUREcan not be combined with any other Transact-SQL
> > statement in a single batch, I was wondering if there were any way to
> > achieve something like this :
> > (..)
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...