October 3, 2009 at 6:31 pm
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
October 3, 2009 at 7:40 pm
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
Change is inevitable... Change for the better is not.
October 3, 2009 at 7:43 pm
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
Change is inevitable... Change for the better is not.
October 3, 2009 at 7:49 pm
DOH! Thanks for pointing out the obvious 🙂 I've used dynamic SQL loads and completely forgot about it here. lol, yip its me again.
October 3, 2009 at 9:37 pm
No problem, Dale. Thanks for the feedback on this and the other post.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2009 at 12:00 am
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]
October 5, 2009 at 12:22 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply