CODEWITHSUNDEEP
sqlsql-servert-sql
noamyg
noamyg

Reputation: 3104

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!

Upvotes: 3

Views: 1577

Answers (2)

gofr1
gofr1

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

BeanFrog
BeanFrog

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

Related Questions