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

  • 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

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

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

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

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

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

  • 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