We help IT Professionals succeed at work.

Populating WHERE clause in Dynamic query

swaggrK
swaggrK asked
on
In the following query I am having difficulty populating the WHERE clause.
How do I populate a_ActorID without hardcoding over a thousand a_ActorID's? 

DECLARE @cols  AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)

SET @cols =   (SELECT DISTINCT 
                      ',' 
                     + ' COUNT( case when ap.a_AssignmentsIdName='
                     + ''''
                     + a_AssignmentsIdName 
                     + ''''
                     + ' then ap.a_ActorID end ) as '
                     + QUOTENAME(a_AssignmentsIdName) 
                   FROM a_partsandincrements
                   WHERE a_ActorID IN ('143939', '128473')
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)') 

set @query = 
    + ' SELECT ap.a_ActorID , ap.a_ActorIdentifierName as Actor'
    +     @cols
    + ' FROM a_partsandincrements AS ap'
    + ' WHERE ap.a_ActorID IN (''143939'', ''128473'')'
    + ' GROUP BY ap.a_ActorID , ap.a_ActorIdentifierName'


execute(@query)
;

Open in new window

Comment
Watch Question

View Solutions Only

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Is there a lookup table somewhere of these Actor_ID's, where you can do a JOIN on that table, which would only return rows where there is a match?

Do the Actor_ID's have some other attribute that can be made in a WHERE clause?  (Where actor.hair='Blonde', actor.genre='Scary', whatever?)
ste5anSenior Developer

Commented:
This makes no sense. As you're having a GROUP BY clause, you cannot add columns to your SELECT without changing the GROUP BY.

Please post a concise and complete example. This includes table DDL and sample data INSERT statements as runnable T-SQL script.
IT Engineer
Distinguished Expert 2017
Commented:
set @query =
    + ' SELECT ap.a_ActorID , ap.a_ActorIdentifierName as Actor'
This will return an error since the first thing is plus signal when it's expected a variable or a string.
+     @cols
This can also originate an error if the subquery returns more than one row.
  + ' GROUP BY ap.a_ActorID , ap.a_ActorIdentifierName'
This will definitely return an error since the number o columns doesn't match with the number of columns in the SELECT. Also you won't need to GROUP BY if there's no aggregate function.
swaggrK

Author

Commented:
Thank you