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?

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

swaggrKAsked:
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.
0
 
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?)
0
 
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.
0
 
swaggrKAuthor Commented:
Thank you
0
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.