Alter procedure if not exists - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Alter procedure if not exists

Author

Topic

roggey
Starting Member

4 Posts

Posted - 2008-09-22 : 11:02:34

Hello

I want to do the following:


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myname]') AND type in (N'P', N'PC'))
	begin
		CREATE  PROCEDURE [dbo].[myname]
			@DateValidFrom datetime,
			@DateValidTo datetime
		AS
		select * from myTable t
		where 
		t.[Date Valid] >=    @DateValidFrom
		And t.[Date Valid]< dateadd(day,1, @DateValidTo)
		


		go

	end
else
	begin
		ALTER PROCEDURE [dbo].[myname]
			@DateValidFrom datetime,
			@DateValidTo datetime
		AS
		select * from myTable t
		where 
		t.[Date Valid] >=    @DateValidFrom
		And t.[Date Valid]< dateadd(day,1, @DateValidTo)
		

		go
	end

When i want to run it it say's the following:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@DateValidFrom".
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'else'.
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@DateValidFrom".
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'end'.

I'm using MS-SQL server 2005.

The problem is, because of some replication between some servers, i can not use drop.

Can someone help me, to make an sql-stament so i can check if a stored procedure exists and alter it, or if not exists create it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 11:07:13

Remove the go statements and try.

roggey
Starting Member

4 Posts

Posted - 2008-09-24 : 04:50:37

Hello

When I'm removing the go's I get get same error.

I have tried to run the create and alter staments sepratly, and they are working fine. The if staments alone is also working fine. Just in combination, there is the problem.

Anyone how have a solution?

Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-24 : 09:03:49

why not just drop it if it exists, and then create? That's pretty much the standard way to do this kind of thing.

if exists(...YourProc ..) drop proc YourProc

go

Create Proc YourProc ....

- Jeff
http://weblogs.sqlteam.com/JeffS

roggey
Starting Member

4 Posts

Posted - 2008-09-25 : 15:57:37

Hello

Because of some data-replication, it is not possible to drop any-thing.

We are not resposible for the technical set-up of the sql-server.

I know, that the standrad way is to drop and create a new, but i can not do this.

mfg

roggey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-25 : 16:54:45

Then you would have to do it like this:


IF NOT EXISTS ( ... MyProc... ) CREATE PROC MyProc AS SELECT 1

GO

ALTER PROC MyProc  
... your "real" proc definition here ...

In other words, just add a dummy CREATE if it doesn't exist, and then immediately do your ALTER with the real definition.

- Jeff
http://weblogs.sqlteam.com/JeffS

swatinagpal
Starting Member

2 Posts

Posted - 2008-09-26 : 05:57:55

Even this will not work as

CREATE PROCEDURE must be the first statement in a query batch.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-09-26 : 07:53:03

It's not possible to create a stored procedure conditionally without using dynamic sql.
Here is my idea.
Any corrections are welcome ;o)

declare @crealter varchar(6)
declare @sqlstmt varchar(max)
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myname]') AND type in (N'P', N'PC'))
select @crealter = 'CREATE'
ELSE
select @crealter = 'ALTER'

--print @crealter
select @sqlstmt =
@crealter
+ ' PROCEDURE [dbo].[myname]'
+ ' @DateValidFrom datetime,'
+ ' @DateValidTo datetime'
+ ' AS'
+ ' select * from myTable t'
+ ' where'
+ ' t.[Date Valid] >= ' + '''' + convert(varchar(30),isnull(@DateValidFrom,getdate())) + ''''
+ ' And t.[Date Valid]< dateadd(day,1,' + '''' + convert(varchar(30),isnull(@DateValidTo,getdate())) + '''' + ')'

--print @sqlstmt

Greetings
Webfred

Planning replaces chance by mistake

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 08:16:24

As Jeff pointed out earlier, this is a standard way

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'myname')
	DROP PROCEDURE dbo.myname
GO
CREATE PROCEDURE	dbo.myname
(
	@DateValidFrom datetime,
	@DateValidTo datetime
)
AS

SET NOCOUNT ON

SELECT	*
FROM	myTable
WHERE	[Date Valid] >= @DateValidFrom
	AND [Date Valid] < dateadd(day, 1, @DateValidTo)
GO

However, since you have some kind of replication going on, you can use this instead (create the dummy stored procedure dynamically)

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'myname')
	EXEC ('CREATE PROC dbo.myname AS SELECT 1')
GO
ALTER PROCEDURE	dbo.myname
(
	@DateValidFrom datetime,
	@DateValidTo datetime
)
AS

SET NOCOUNT ON

SELECT	*
FROM	myTable
WHERE	[Date Valid] >= @DateValidFrom
	AND [Date Valid] < dateadd(day, 1, @DateValidTo)
GO

E 12°55'05.63"
N 56°04'39.26"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-09-26 : 10:04:57

Oh St. Peso!
That is much more pretty than my idea.

Greetings
Webfred

Planning replaces chance by mistake

roggey
Starting Member

4 Posts

Posted - 2008-09-29 : 08:19:49

HEllo

Thanks to PESO.

This is a big help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources