T-SQL function with dynamic SELECT (not possible) - solved with procedure instead

Question

I've managed to use EXEC sp_executesql in a one off statement to do a dynamic lookup, but am unable to adjust the code to create a function since EXEC is not allowed in functions. It works in procedures and I've managed to get output via PRINT for a single lookup by using a temporary table, but really that was just me struggling to find a workaround. Ideally I'd like to be able to create a scalar-value function.

The reason that I need a dynamic lookup is because the column name is stored in another table.

Here's a quick breakdown of the tables:

Questions:

Columns: Q_Group, Q_Nbr, Question_Desc, Data_Field
Sample data: 'R3', 5, 'Do you have any allergies?', 'TXT_04'

Responses:

Columns: Order_Nbr, Q_Group, TXT_01, TXT_02, TXT_03, TXT_04, etc.
Data: 999, 'R3', 'blah', 'blah', 'blah', 'NO'

Orders will be assigned a particular set of questions 'Q_Group' and often a particular question will be the same across various different sets of questions. The problem is that when the set/groups of questions were set up, the questions may not have been added in the same order, and thus the responses go into different columns.

So here's where I'm at...

I can get 'TXT_04' from the Data_Field column in Questions and use EXEC sp_executesql to do a lookup for a single order, but am struggling to find a way to accomplish this as a function of some sort.

DECLARE @col_name VARCHAR(6)
DECLARE @sql VARCHAR(100)

SET @col_name = SELECT Data_Field FROM QUESTIONS WHERE Q_Group = 'R3'
   AND Question_Desc = 'Do you have any allergies?'

SET @sql = 'SELECT ' + @col_name + ' FROM RESPONSES WHERE Order_Nbr = 999'

EXEC sp_executesql @sql

I'm just at a loss as to how this could be incorporated into a function so that I could get responses for several orders in a result set. Any workarounds possible? Maybe I'm totally off base using EXEC sp_executesql?

Thanks.

Edit...

Okay, I've changed the title to reflect that I'm going to consider this solved with a procedure instead of a function, as it ended up getting the output that I wanted. Which was a table with all of the corresponding responses.

Here's the code that I settled on. I decided to use LIKE to match the Question_Desc instead of equals, and then included the Question_Desc in the results, so that it could be used a bit more broadly. Thankfully it's pretty quick to run currently. Although that could always change as the database grows!

CREATE PROCEDURE get_all_responses (@question_txt VARCHAR(255))
AS
   DECLARE @response_col VARCHAR(35)
   DECLARE @t TABLE (order_nbr int, question_txt VARCHAR(255), response_col VARCHAR(35), response VARCHAR(255))
   DECLARE @i TABLE (id INT PRIMARY KEY IDENTITY(1,1), response_col VARCHAR(35))
   DECLARE @u TABLE (order_nbr int, response VARCHAR(255))
   DECLARE @sql VARCHAR(200)

   INSERT @t
        SELECT Order_Nbr, Question_Desc, Data_Field, NULL
        FROM Responses
        JOIN (
            SELECT Q_Group, Question_Desc, Data_Field
            FROM Questions
            WHERE Question_Desc LIKE @question_txt
            ) #Q ON Q_Group = #Q.Q_Group
        WHERE Q_Group <> '0'
        ORDER BY Data_Field, Order_Nbr

    -- Stop if no results found and return empty result set
   IF (SELECT COUNT(*) FROM @t) = 0
        BEGIN
            SELECT order_nbr, question_txt, response FROM @t
            RETURN
        END

    INSERT @i SELECT response_col FROM @t GROUP BY response_col

    DECLARE @row_nbr int
    DECLARE @last_row int
    SET @row_nbr = 1
    SET @last_row = (SELECT COUNT(*) FROM @i)

   -- Iterate through each Data_Field found
    WHILE @row_nbr <= @last_row
        BEGIN
            SET @response_col = (SELECT response_col FROM @i WHERE id = @row_nbr)
            SET @sql = 'SELECT Order_Nbr, ' + @response_col + ' FROM Responses WHERE NullIf(' + @response_col + ','''') IS NOT NULL'

            INSERT INTO @u
            EXEC (@sql)

            UPDATE @t
            SET response = y.response
            FROM @t AS x
            INNER JOIN @u AS y ON x.order_nbr = y.order_nbr

            SET @row_nbr = @row_nbr + 1
        END

   -- Remove results with no responses
    DELETE FROM @t WHERE response IS NULL

    SELECT order_nbr, question_txt, response FROM @t
RETURN

I found [this other stackoverflow question](http://stackoverflow.com/questions/150552/executing-dynamic-sql-in-a-sqlserver-2005-function) which is very similar, but unless I'm missing something I didn't find a usable solution from it. Which perhaps is the answer, but I'm really hoping there's another way to accomplish this! /fingerscrossed — Mark C, Mar 14 '14 at 00:40
@Jayvee Yeah Order_Nbr and Question_Desc. I'd grab the Q_Group from the Responses table, then check if there's a matching Question_Desc in the Question table, and finally get the column to lookup from the corresponding Data_Field (if found at all) to return the response to the question. I found a previous question about [returning tables from procedures](http://stackoverflow.com/questions/1443663/how-to-return-temporary-table-from-stored-procedure), which I thought could only be done with functions, so I'm going to see if that's works and will update later today. — Mark C, Mar 14 '14 at 17:11

score 0 · Accepted Answer · answered Mar 14 '14 at 03:44

You will not be able to execute dynamic SQL from within a function but you could do this with a stored procedure and capture the output.

    DECLARE @col_name VARCHAR(6), @param NVARCHAR(50), @myReturnValue VARCHAR(50)
    SET @param  = N'@result VARCHAR(50) OUTPUT'

    DECLARE @sql VARCHAR(100)

    SET @col_name = SELECT Data_Field FROM QUESTIONS WHERE Q_Group = 'R3'
       AND Question_Desc = 'Do you have any allergies?'

    SET @sql = 'SELECT @result = ' + @col_name + ' FROM RESPONSES WHERE Order_Nbr = 999'

    EXEC sp_executesql @sql, @param, @result = @myReturnValue output
--manipulate value here
print @myReturnValue

You could also create a temp table and do an insert into from exec sp_executesql.

Yeah I came up with something pretty similar and fiddled with temporary tables too, but I'm unsure of how to capture output from procedures and add it to a result set for a list of multiple orders. Although, I suppose that I could try the temporary table option again and loop though each of the records and insert the responses that way. Thanks for the assistance Dennis. I'll keep the question as unanswered a bit longer just to see if there are any other suggestion. Bah, I can't even upvote yet! — Mark C, Mar 14 '14 at 07:49

T-SQL function with dynamic SELECT (not possible) - solved with procedure instead

1 Answers1