SQL Server не позволяет выполнять подзапрос как динамический SQL

Я попытался сделать динамический SQL, который выполняет все задачи для некоторых пользователей

Процедура [GetAllSubExecutorsByUserId] возвращает идентификаторы всех подальтернов текущего пользователя. Я записываю эти идентификаторы во временную таблицу, и после этого я хочу сделать динамический SQL, чтобы получить все задачи из таблицы [задачи], где столбец «Исполнитель» имеет значение IN эта временная таблица

Я написал следующий запрос:

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

Но когда я запускаю его, он выдает ошибку:

Подзапрос вернул более одного значения. Это не разрешено, если подзапрос следует за =,! =, <, <=,>,> = Или когда подзапрос используется как выражение.

И я не могу понять, как это исправить, потому что, если я запускаю тот же запрос (который не является динамическим SQL, он отлично работает)

запрос, который работает, статичен:

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)

Но мне нужен диинамический SQL ... Помогите, пожалуйста, решить эту проблему.

Спасибо.

sql dynamic

Внутренний запрос select Id from @tmp в этом случае не будет создавать для вас динамический список идентификаторов. Вы работаете с разными прицелами. Вам нужно что-то, что построит для вас этот список идентификаторов, а затем объединит этот список с остальной частью вашего динамического создания SQL.

Он работает в вашем статическом случае, потому что внутренний запрос находится в той же области, что и остальная часть вашего SQL.

Вы можете обойти это, изменив @tmp на временную таблицу вместо табличной переменной и удалив конкатенацию.

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

Это перемещает область временной таблицы и позволяет вам сгенерировать один оператор SQL для выполнения вашего запроса, независимо от того, сколько записей находится в таблице.

Вы также можете уйти от динамического SQL, сделав это.

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