Post reply

dynamic SQL in a function

  • December 6, 2009 at 9:14 am

    #140439

    I can do this in a procedure - build a "declare myCursor as cursor for ..." string dynamically, adding some values to table names from an outer cursor.

    I insert the final value into a temporary table, and select it out; no problem.

    In this table, the value is an html table consisting of a single string, because my goal here is that I am able to select this entire html table as a single object ... sort of a homespun grid object ... and select it from another procedure as one value among many.

    I can't select the procedure from another procedure, or set a variable equal to it. I need to use a function for that.

    But in the function, I can't execute the (@SQL) string that I created dynamically.

    There must be a way out of this bind. Can someone help?

    Thanks!

  • December 6, 2009 at 9:47 am

    #1088272

    My recommendation would be to post the code and some sample data according to the first link in my signature below. Someone may be able to help you avoid both the cursor and the dynamic SQL.

    I'm not sure, but I believe you may be able to use EXEC sp_ExecuteSQL to get around the dynamic SQL thing in a function because functions do allow certain "system" stored procedures to be exec'ed, but I haven't tried it.

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

Viewing 2 posts - 1 through 1 (of 1 total)

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