SQL Server doesn't allow to execute subquery as dynamic SQL

Question

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.

Justin Pihony · Accepted Answer · 2012-03-15 15:03:27Z

3

This is because you are trying to set a string value from a select that is returning more than one value. SQL does not know how to turn this list into a comma delineated one. In the prepared statement it is running as a true IN against a returned 'table', so it can run SET operators against that subquery table. In the dynamic query, you are essentially trying to create something like this, though:

...IN (1,2,3)

You must rewrite the dynamic query like below to get it to run just like the prepared statement:

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

If you do this, then you will have to pass the table variable into the exec though, but you will have to use sp_executesql. So, your code becomes:

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 ;

However, another, cleaner option would be to use a temp table (not variable), as it will stay alive throughout your connection (even in child queries)

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 ;

edited Mar 15, 2012 at 15:03

answered Mar 15, 2012 at 13:36

Justin Pihony

67.2k20 gold badges154 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

mihai Over a year ago

as i understand, i should get the result of "select Id from @tmp" subquery as a CSV string ... but what about sp_executesql ?>

Justin Pihony Over a year ago

I do not know where you got that understanding, but that is NOT true that a subquery will convert to a CSV. I will update my answer to show how this can be used for sp_executesql as described

mihai Over a year ago

Justin, at the line : SET @SQLParamSetup = "'@tmp TABLE ( Id VARCHAR(10))' it is something wrong... if I remove those double quotes (") there appears the error message "incorrect sintax near sp_executesql" can you suggest me how to adapt... thank you

Justin Pihony Over a year ago

Sorry, you have to EXEC the sproc that executes sql :p. I have updated the code

mihai Over a year ago

Justin, can you explain, why with EXEC sp_executesql @SQL, @SQLParamSetup, @tmp ; also it doesn't execute.. I've created #Executors table as temporary, the @SQL query i've changed to be NVARCHAR (because sp_executesql expects only NVARCHAR, NTEXT and NCHAR)

DECLARE @SQL NVARCHAR(max)  SET @SQL = 'SELECT * FROM tasks '  SET @SQL = @SQL + ' WHERE tasks.Executor IN (SELECT Id FROM #Executors)'   DECLARE @ParamDef NVARCHAR(1000)  SET @ParamDef = '#Executors TABLE (Id NVARCHAR(10))'  Execute sp_executesql @SQL, @ParamDef, #Executors

it gives _Incorrect syntax near '#Executors'.

|

Darren Kopp · Accepted Answer · 2012-03-15 13:41:43Z

0

Use sp_executesql and you can pass in the parameter.

answered Mar 15, 2012 at 13:41

Darren Kopp

77.8k9 gold badges81 silver badges93 bronze badges

1 Comment

Darren Kopp Over a year ago

Well what you have there is really simple and doesn't need dynamic sql so i'm guessing you have a more complicated query, but you could pass your table variable as a parameter to be used by the dynamic sql

dinesh kumar · Accepted Answer · 2020-11-12 06:42:42Z

0

declare @ddto as char(10)='2020/10/01',
set @month= (select month(@ddto))
set @year =(select year(@ddto))

you can change in the dynamic query as following:
select column1 where
Material_Issued_to_div='''+ @div+''' and 
month(indent_date)='''+@month+'''       and 
year(indent_date)='''+@year+'''

answered Nov 12, 2020 at 6:42

dinesh kumar

2212 silver badges4 bronze badges

Collectives™ on Stack Overflow

SQL Server doesn't allow to execute subquery as dynamic SQL

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related