Create A stored proc template that checks if exists then Alter otherwise create

Question

Create A stored proc template that checks if exists then Alter otherwise create

pamozer

SSCarpal Tunnel

Points: 4943
More actions
November 21, 2012 at 12:04 pm

#271138

I am trying to create a template for our developers for stored procedures. I want the logic to be the following:
If Exists(Select * from sys.objects where name='sproc')
Alter Procedure Sproc
Else
Create Procedure Sproc
Is this possible? I am drawing a blank. I can't use a drop and create because we have replication in play for some of our databases.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

pamozer (11/21/2012)
I am trying to create a template for our developers for stored procedures. I want the logic to be the following:
If Exists(Select * from sys.objects where name='sproc')
Alter Procedure Sproc
Else
Create Procedure Sproc
Is this possible? I am drawing a blank. I can't use a drop and create because we have replication in play for some of our databases.
Thanks

What does replication have to do with dropping and recreating stored procs?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pamozer SSCarpal Tunnel Points: 4943 More actions · Answer 2

If you try to drop a stored procedure that is part of replication you get the following error

Cannot drop the procedure 'ReportDataProvider_ArAgingSummary_New_V1' because it is being used for replication.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Oh my bad...i think my brain has already decided to call it quits this week. You are replicating the proc. As you have likely discovered you can't quite do what your template is doing. ddl statements have to run in a batch. In order to do this you would have to use dynamic sql as the body of each of your conditions. :w00t:

This is not something I would recomment but it will work.

declare @sql nvarchar(max)

if exists(select * from sys.objects where name = 'TestProc')

begin

set @sql = 'alter procedure TestProc as begin select 1 end'

exec sp_executesql @sql

end

else

begin

set @sql = 'create procedure TestProc as begin select 3 end'

exec sp_executesql @sql

end

exec testproc

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pamozer SSCarpal Tunnel Points: 4943 More actions · Answer 4

Yeah, that's what I was afraid of. And introducing dynamic sql is not the way I want to go.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Agreed.

You could maybe tweak it slightly to avoid being forced to create your alter code as dynamic sql. Just have your template generate a placeholder proc and then always alter it. Something like this...

declare @sql nvarchar(max), @MyProc varchar(255) = 'TestProc'

if not exists(select * from sys.objects where name = @MyProc)

begin

--This just generates a placeholder proc if it doesn't exist yet.

set @sql = 'create procedure ' + @MyProc + ' as begin select 1 end'

exec sp_executesql @sql

end

go

alter procedure TestProc(@Parm int) --added a Parameter to demonstrate you can change this up however you want.

as

begin

select @Parm

end

go

exec testproc 123

Still kind of nasty but it limits the dynamic sql to just creating the placeholder proc. Not sure this helps as it seems a bit oddball but maybe it will help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pamozer SSCarpal Tunnel Points: 4943 More actions · Answer 6

Actually, that might be the answer. I'll play around with it.

thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

pamozer (11/21/2012)
Actually, that might be the answer. I'll play around with it.
thanks

You are welcome. Hope something in there works for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 8

pamozer (11/21/2012)
I am trying to create a template for our developers for stored procedures. I want the logic to be the following:
If Exists(Select * from sys.objects where name='sproc')
Alter Procedure Sproc
Else
Create Procedure Sproc
Is this possible? I am drawing a blank. I can't use a drop and create because we have replication in play for some of our databases.
Thanks

I wouldn't do this on a bet. If the developers can't figure out this simple thing, then there's also the chance that they've name a spoc after something that already exists and it would be overwritten by the resulting ALTER.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 9

pamozer (11/21/2012)
If you try to drop a stored procedure that is part of replication you get the following error
Cannot drop the procedure 'ReportDataProvider_ArAgingSummary_New_V1' because it is being used for replication.

Can you disable replication, alter the SP and then turn it back on again?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

Sean Lange (11/21/2012)
.... As you have likely discovered you can't quite do what your template is doing. ddl statements have to run in a batch. In order to do this you would have to use dynamic sql as the body of each of your conditions.
...

First of all, as the all others, I would also strongly suggest not to do what you are trying to do for all reasons specified by previous posters.

Saying above, just to make facts clear: It's possible to make DDL statements conditional, try to run the following twice and you will see how stored proc will be created and altered by the "same" conditional batch:

If Exists(Select * from sys.objects where name='mysproc')

SET NOEXEC OFF;

else

SET NOEXEC ON;

GO

Alter Procedure mysproc

as

BEGIN

select 2

END

GO

SET NOEXEC OFF;

GO

If Exists(Select * from sys.objects where name='mysproc')

SET NOEXEC ON;

else

SET NOEXEC OFF;

GO

Create Procedure mysproc

AS

BEGIN

Select 1

END

GO

SET NOEXEC OFF;

GO

-- see the proc text

exec sp_helptext mysproc

GO

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]