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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
swaggrKAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.