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.