Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

Thank you.

share|improve this question

1 Answer 1

The inner query select Id from @tmp will not build a dynamic list of ID's for you in this case. You're working with different scopes. You need something that will construct that list of ID's for you and then concatenate the list with the rest of your dynamic SQL creation.

It works in your static case because the inner query is within the same scope as the rest of your SQL.

You could get around this by changing @tmp to a temp table instead of a table variable and removing the concatenation.

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

This moves the scope of the temp table and lets you generate a single SQL statement to perform your query, regardless of how many records are in table.

You may also be able to get away from the dynamic SQL by doing that.

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.