SQL Server doesn't allow to execute subquery as dynamic SQL

Question

I tried to make a dynamic SQL which gets all tasks for some users

The procedure [GetAllSubExecutorsByUserId] returns IDs of all subalterns of curent user I write these IDs into a temporary table, and after that I want to make a dynamic SQL to get all tasks from [tasks] table where "Executor" column has the value IN this temporary table

The query I wrote follows:

DECLARE @UserId VARCHAR(10) = 72;

DECLARE @tmp TABLE  ( Id VARCHAR(10));
INSERT @tmp exec [dbo].[GetAllSubExecutorsByUserId] @Source = @UserId;

DECLARE @SQL VARCHAR(max);

SELECT @SQL = 'SELECT * FROM tasks ';
SELECT @SQL = @SQL + 'WHERE Executor IN (' + (select Id from @tmp) + ')';

EXEC(@SQL);

But when i run it , it gives an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

And i can't understand how to fix it, because if i run the same query (which is not a dynamic SQL it works perfectly)

the query which works is static:

DECLARE @UserId VARCHAR(10) = 72;

DECLARE @tmp TABLE  ( Id VARCHAR(10));
INSERT @tmp exec [dbo].[GetAllSubExecutorsByUserId] @Source = @UserId;

SELECT * FROM tasks WHERE Executor IN (select Id from @tmp)

But I need a diynamic SQL... Help me please to resolve this problem.

Thank you.

score 1 · Answer 1 · answered Mar 15 '12 at 14:46

The inner query select Id from @tmp will not build a dynamic list of ID's for you in this case. You're working with different scopes. You need something that will construct that list of ID's for you and then concatenate the list with the rest of your dynamic SQL creation.

It works in your static case because the inner query is within the same scope as the rest of your SQL.

You could get around this by changing @tmp to a temp table instead of a table variable and removing the concatenation.

DECLARE @UserId VARCHAR(10) = 72;

CREATE TABLE #tmp ( Id VARCHAR(10));
INSERT #tmp exec [dbo].[GetAllSubExecutorsByUserId] @Source = @UserId;

DECLARE @SQL VARCHAR(max);

SELECT @SQL = 'SELECT * FROM tasks ';
SELECT @SQL = @SQL + 'WHERE Executor IN (select Id from #tmp)';

EXEC(@SQL);

DROP TABLE #tmp

This moves the scope of the temp table and lets you generate a single SQL statement to perform your query, regardless of how many records are in table.

You may also be able to get away from the dynamic SQL by doing that.

DECLARE @UserId VARCHAR(10) = 72;

CREATE TABLE #tmp ( Id VARCHAR(10));
INSERT #tmp exec [dbo].[GetAllSubExecutorsByUserId] @Source = @UserId;

SELECT * FROM tasks WHERE Executor IN (select Id from #tmp)

DROP TABLE #tmp

SQL Server doesn't allow to execute subquery as dynamic SQL

1 Answers1