Reputation: 3104
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!
Upvotes: 3
Views: 1577
Reputation: 15997
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
Upvotes: 2
Reputation: 2315
You need to take your dynamic sql one step deeper, and include the id number.
This script will generate a select statement for each row, and union them all together. You then run this whole select in one go to get your output.
--set up a string variable to store your query
declare @tsql nvarchar(max);
set @tsql = '';
-- populate it with the string statements
select @tsql = @tsql + 'select ' + cast(t.id as nvarchar(10)) + ' as id , (' + dbo.queryToExecute(t.id, t.ColumnToFetch, t.TableToFetchFrom) + ') QueryToExecute union ' from Table t
--chop off the final "union"
select @tsql = left (@tsql,len(@tsql)-7);
--and run
EXEC sp_executesql @tsql
Upvotes: 0