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

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.

Solution

This is because you are trying to set a string value from a select that is returning more than one value. SQL does not know how to turn this list into a comma delineated one. In the prepared statement it is running as a true IN against a returned 'table', so it can run SET operators against that subquery table. In the dynamic query, you are essentially trying to create something like this, though:

...IN (1,2,3)

You must rewrite the dynamic query like below to get it to run just like the prepared statement:

'WHERE Executor IN (select Id from @tmp)'

If you do this, then you will have to pass the table variable into the exec though, but you will have to use sp_executesql. So, your code becomes:

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)';

DECLARE @SQLParamSetup VARCHAR(150);
SET @SQLParamSetup = '@tmp TABLE  ( Id VARCHAR(10))'


EXEC sp_executesql @SQL, @SQLParamSetup, @tmp ;

However, another, cleaner option would be to use a temp table (not variable), as it will stay alive throughout your connection (even in child queries)

DECLARE @UserId VARCHAR(10) = 72;

CREATE TABLE #tmp  ( Id VARCHAR(10));
INSERT INTO #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 ;