Populating WHERE clause in Dynamic query

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?


                     + ' 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'


Open in new window

Who is Participating?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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 DeveloperCommented:
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.
swaggrKAuthor Commented:
Thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.