Is it possible to create a SP from inside a SP?

Question

Is it possible to create a SP from inside a SP?

Dale Burrell

SSCommitted

Points: 1737
More actions
October 3, 2009 at 6:31 pm

#214951

Hi,
Is it possible to create a stored procedure from within a stored procedure? The reason I ask is that when I update the CRL assemblies I often need to drop all dependent SP's then create them again. I was hoping to put the whole update inside an SP e.g. drops SP's, drop assemblies, load assemblies, create SPs.
Is this possible? I have it all working aside from creating the procedures.
Cheers
Dale

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 1

Yes, it is possible... using dynamic SQL.

What you're going through sounds like a good reason to not use CLR's. What do they do? I ask because there may be a less expensive way to solve the problems you're trying to solve through the CLR's.

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

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

Heh... Never mind, Dale. I just remembered who you are and what you're using the CLR's for.

Like I said... dynamic SQL... unless you want to write another CLR to do it for you. 😀

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

Dale Burrell SSCommitted Points: 1737 More actions · Answer 3

DOH! Thanks for pointing out the obvious 🙂 I've used dynamic SQL loads and completely forgot about it here. lol, yip its me again.

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 4

No problem, Dale. Thanks for the feedback on this and the other post.

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

Dale Burrell (10/3/2009)
The reason I ask is that when I update the CRL assemblies I often need to drop all dependent SP's then create them again.

By the way, the easiest way to fix this problem is to wrap your CLR Stored procedures in SQL stored procedures, and then have everything else call the SQL wrapper instead. This way, at most all you have to do is to either DROP the wrapper or ALTER it to not reference the CLR proc.

I was hoping to put the whole update inside an SP e.g. drops SP's, drop assemblies, load assemblies, create SPs.
Is this possible? I have it all working aside from creating the procedures.

Yes, it is very possible, I do it all the time. As Jeff says, the trick is to use Dynamic SQL.

[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]

Dale Burrell SSCommitted Points: 1737 More actions · Answer 6

Thanks for that - actually, I could be wrong, but as far as I can tell one *must* wrap a CLR procedure within a T-SQL procedure to be able to use it. So yes the dropping/creating is fairly simple, and in fact now that things are more stable I can mostly just alter the assembly rather than drop/create it. Cheers again.