☰

SQL Server 不允许将子查询作为动态 SQL 执行

tag-icon • tag-icon sql dynamic
SQL Server 不允许将子查询作为动态 SQL 执行

我尝试制作一个动态 SQL，获取某些用户的所有任务

过程 [GetAllSubExecutorsByUserId] 返回当前用户的所有下属的 ID，我将这些 ID 写入临时表，然后我想制作一个动态 SQL 来从 [tasks] 表中获取所有任务，其中“Executor”列具有此临时表中的值

我写的查询如下：

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

但是当我运行它时，它给出一个错误：

子查询返回了多于 1 个值。当子查询位于 =、!=、<、<=、>、>= 之后或子查询用作表达式时，不允许出现这种情况。

而且我不明白如何修复它，因为如果我运行相同的查询（这不是动态 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...请帮我解决这个问题。

谢谢。

答案1

在这种情况下，内部查询select Id from @tmp不会为您构建动态 ID 列表。您正在使用不同的范围。您需要一些东西来为您构建该 ID 列表，然后将该列表与其余的动态 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

相关内容