user3367818 user3367818 - 3 months ago 11
SQL Question

Execute a query returned from a scalar function

I have a scalar function that returns a query. The scalar function must return the query itself and not its' results, since it uses dynamic SQL and the variables are used as column names and table names.

So, I have something like this:

SELECT t.id,
dbo.queryToExecute(t.id, t.ColumnToFetch, t.TableToFetchFrom) QueryToExecute
FROM Table t


Which returns

| ID | QueryToExecute
| 1 | SELECT ColumnName1 FROM Table1 WHERE id = 1
| 2 | SELECT ColumnName2 FROM Table2 WHERE id = 2


While "QueryToExecute" returns a single value.
I want to do something like:

SELECT t.id,
EXEC(dbo.queryToExecute(t.id, t.ColumnToFetch, t.TableToFetchFrom)) ExecutedQuery
FROM Table t


So the result set will be:

| ID | ExecutedQuery
| 1 | Jacob
| 2 | Sarah


How can I do that?

I already have a stored procedure that gets the job done when I need to run individually, but I wanted to have the same thing on a scalar function in order to be able to embed the sub-results in more complex queries.

Thanks!

Answer

You need to use dynamic SQL:

DECALRE @sql nvarchar(max)

SELECT  @sql = COALESCE(@sql,'') + 
            REPLACE(
                dbo.queryToExecute(t.id, t.ColumnToFetch, t.TableToFetchFrom),
                'SELECT ',
                'SELECT '+ CAST(t.ID as nvarchar(max) +' as ID, '
                ) +' UNION ALL ' 
FROM Table t

SELECT @sql = LEFT(@sql,LEN(@sql)-LEN('UNION ALL '))

EXEC sp_executesql @sql

This will get all query's in one batch and execute it