O SQL Server não permite executar a subconsulta como SQL dinâmico

Question

Eu tentei fazer um SQL dinâmico que obtenha todas as tarefas para alguns usuários

O procedimento [GetAllSubExecutorsByUserId] retorna IDs de todos os subalternos do usuário curent Eu escrevo esses IDs em uma tabela temporária, e depois disso eu quero fazer um SQL dinâmico para obter todas as tarefas da tabela [tasks] onde a coluna "Executor" o valor nesta tabela temporária

A consulta que escrevi segue:

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

Mas quando eu executo, dá um erro:

A subconsulta retornou mais de 1 valor. Isso não é permitido quando a subconsulta segue =,! =, & Lt ;, < =, & gt ;, > = ou quando a subconsulta é usada como uma expressão.

E eu não consigo entender como consertá-lo, porque se eu executar a mesma consulta (que não é um SQL dinâmico funciona perfeitamente)

a consulta que funciona é estática:

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)

Mas eu preciso de um SQL dinâmico ... Ajude-me por favor a resolver este problema.

Obrigado.

    

Answer 1

A consulta interna select Id from @tmp não criará uma lista dinâmica de IDs para você neste caso. Você está trabalhando com diferentes escopos. Você precisa de algo que construirá essa lista de IDs para você e, em seguida, concatenará a lista com o restante de sua criação de SQL dinâmica.

Funciona no seu caso estático porque a consulta interna está dentro do mesmo escopo que o resto do seu SQL.

Você poderia contornar isso alterando @tmp para uma tabela temporária em vez de uma variável de tabela e removendo a concatenação.

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

Isso move o escopo da tabela temporária e permite gerar uma única instrução SQL para realizar sua consulta, independentemente de quantos registros estão na tabela.

Você também pode se livrar do SQL dinâmico fazendo isso.

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