user3367818 user3367818 - 1 month ago 4x
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:

dbo.queryToExecute(, 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:

EXEC(dbo.queryToExecute(, 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.



You need to use dynamic SQL:

DECALRE @sql nvarchar(max)

SELECT  @sql = COALESCE(@sql,'') + 
                dbo.queryToExecute(, 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