November 21, 2012 at 12:04 pm
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
November 21, 2012 at 12:19 pm
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/
November 21, 2012 at 12:25 pm
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.
November 21, 2012 at 12:39 pm
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/
November 21, 2012 at 1:17 pm
Yeah, that's what I was afraid of. And introducing dynamic sql is not the way I want to go.
November 21, 2012 at 1:28 pm
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/
November 21, 2012 at 1:47 pm
Actually, that might be the answer. I'll play around with it.
thanks
November 21, 2012 at 1:50 pm
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/
November 21, 2012 at 10:15 pm
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
Change is inevitable... Change for the better is not.
November 21, 2012 at 11:06 pm
pamozer (11/21/2012)
If you try to drop a stored procedure that is part of replication you get the following errorCannot 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 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
November 22, 2012 at 2:44 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply