SQL Server不允许执行子查询的动态SQL

Question

我试图使动态SQL它可以获取所有任务对一些用户 我写这些CURENT用户的所有subalterns的

的程序[GetAllSubExecutorsByUserId]返回标识ID添加到一个临时表，之后，我想使动态SQL摆脱[任务]表中的所有任务，其中“执行人”列在这个临时表中的值

我写的查询如下：

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

卜t当我运行它，它给出了一个错误：

子查询返回多个值。当子查询遵循=，！=，<，< =，>，> =或当子查询用作表达式时，这是不允许的。

，我可以不知道如何解决它，因为如果我运行相同的查询（这不是它的工作原理完全动态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 ... 请帮我解决这个问题。

谢谢。

Answer 1

这是因为您试图从select返回多个值来设置字符串值。 SQL不知道如何将这个列表转换成逗号分隔的列表。在准备好的语句中，它针对返回的“表”运行为真IN，因此它可以针对该子查询表运行SET运算符。在动态查询，你基本上是试图建立这样的事情，虽然：

...IN (1,2,3) 

您必须重写像下面的动态查询得到它就像准备好的语句运行：

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

如果你这样做，那么你将不得不将表变量传递给exec，但是你必须使用sp_executesql。所以，你的代码就变成了：

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 ; 

然而，另一种更简便的是使用临时表（不变量），因为它会在你的连接（即使是在孩子的查询）

活路

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 ; 

Answer 2

使用sp_executesql，您可以传入参数。