Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server 2008
Microsoft SQL Server 2008

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: 2015-03-27

Who is Participating?

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2015-03-27

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.

LVL 66

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented: 2015-03-27

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

LVL 39

ste5anSenior DeveloperCommented: 2015-03-27

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.