Conditional Alteration of Stored Procedurs

Question

Post reply

Conditional Alteration of Stored Procedurs

divecon

SSC Rookie

Points: 49
More actions
March 13, 2008 at 4:48 pm

#184959

I thought I was on the right track with :r but that isn't quite what I need.
The issue is that I only want to alter a stored procedure if certain conditions are met. Since ALTER PROCEDURE must be within its own batch I can use an IF statement to create it. I also can't use an IF statement to branch around the ALTER PROCEDURE since that would require labels outside of scope. My question then becomes: Is there an "exit" statement that I could use and reverse my test to exit if the condition is not met otherwise execute the ALTER PROCEDURE in its own batch.
Thanks in advance,
Jay

Jay Falck, CISSP, CHSS
Unicorn Computing
sqlservercentral@unicorncomputing.com

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

The way that you can do this is to "Nest" your SQL command batches by using [font="Courier New"]EXEC('string')[/font].

This dynamic execution of SQL has its own batch that ends when the [font="Courier New"]EXEC[/font] ends.

So you want something like this:

IF (condition)

BEGIN

Declare @sql varchar(max)

Select @sql = 'ALTER PROCEDURE {name}

AS

{...}

'

EXEC (@sql)

END

More complex needs for separation of batches can be satisfied by sequencing and nesting [font="Courier New"]EXEC('string')[/font] commands.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

Poifect 😉

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

N'yuk! N'yuk! N'yuk!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

divecon SSC Rookie Points: 49 More actions · Answer 4

Thanks for the feed back but that just won't work for the several thousand line stored procedures I'm dealing with.

My original design for this project was to check the condition and alter the stored procedure if the condition were true. Now, I'd just like to NOT the same test and exit the script if the test fails.

Jay Falck, CISSP, CHSS
Unicorn Computing
sqlservercentral@unicorncomputing.com

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Why not? VARCHAR MAX holds 2 billion + characters...

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

divecon (3/13/2008)
Thanks for the feed back but that just won't work for the several thousand line stored procedures I'm dealing with.
My original design for this project was to check the condition and alter the stored procedure if the condition were true. Now, I'd just like to NOT the same test and exit the script if the test fails.

I have sProcs that big and they work just fine (Varchar(MAX) fixes a lot of problems).

Sharepoint has some monsters and that is how they do it.

Go into you databases and script out some of your sProcs. Now look at the scripts. See, that is how the Scripter (and Microsoft) do it.

Two Gigabytes is an awful lot of metadata.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

divecon SSC Rookie Points: 49 More actions · Answer 7

It's late here in TX, USA so I missed the max part. Last question, I hope, how do you handle all the embedded quotes?

Jay Falck, CISSP, CHSS
Unicorn Computing
sqlservercentral@unicorncomputing.com

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

divecon (3/13/2008)
It's late here in TX, USA so I missed the max part. Last question, I hope, how do you handle all the embedded quotes?

You have to double them up. It is confusing at first, but you get used to it soon enough.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

rbarryyoung (3/14/2008)[hr
You have to double them up. It is confusing at first, but you get used to it soon enough.

Or you can do what somebody suggested on one of these forums recently: write your code with a placeholder character (" or |, maybe) instead of the single quote. Then do this

SET @MyStr = REPLACE(@MyStr, '|', '''')

EXEC @MyStr

Apologies if the syntax of REPLACE above isn't correct, but you get the idea.

John

Adam Haines SSC-Insane Points: 23217 More actions · Answer 10

Go into you databases and script out some of your sProcs. Now look at the scripts. See, that is how the Scripter (and Microsoft) do it.

I second this. This is by far the easiest method to create dynamic TSQL, for existing procedures. For some reason, when you script stored procedures via the task context menu (right-click database --> tasks --> generate scripts) Microsoft creates the procedures using dynamic SQL. However, if you right-click on an object and script to new query window it does not.

So much for consistency 😉

My blog: http://jahaines.blogspot.com

RBYoung Right there with Babe Points: 742 More actions · Answer 11

Adam Haines (3/14/2008)
I second this. This is by far the easiest method to create dynamic TSQL, for existing procedures. For some reason, when you script stored procedures via the task context menu (right-click database --> tasks --> generate scripts) Microsoft creates the procedures using dynamic SQL. However, if you right-click on an object and script to new query window it does not.
So much for consistency 😉

Yeah, it's interesting Adam, the reason for the difference is the "Include IF NOT EXISTS" setting.

If this is off then it will not quote the script and do the EXEC(@string) thing. Since it is turned off in the task context menu (with no way to turn it on) you do not see it there. However, if you turn it on in the dialog (or in your default settings) when you are scripting from the database level, then you will get the EXEC(@string) trick.

The puspose of this is exactly the thing that the Original Poster was asking about: They have to do an IF... and conditionally execute something that has to be at the start of a batch (CREATE PROC ...). The usual way of starting a new batch (GO) will not work with conditionals, so they use EXEC(@string) instead.