0

I would like to use the in operator in a dynamic query . At the moment I need someone to point in in the right direction as to how to structure the apostrophes for separating values so that they don't interfere with the apostrophes for the dynamic query structure. This is a section of the query that is giving problems so what I need to know is how where to place the separator so that all of values can be included in dynamic query format as opposed to the regular query format that I have right now

WHERE ai.ImNumber != me.Company 
    AND me.ID IN ('value1','value2','value2','value3','value4','value5')'

My Query is

SET @SQLString = 'select me.EventID , ai.AggregateInfo, '''+@DateBegin+''' AS DateBegin, '''+@EDateEnd+''' AS DateEnd 
FROM tbl_MajorEarners me 
INNER JOIN tbl_currentearners e ON e.EventID = me.EventID 
INNER JOIN tbl_AggregateInfo ai ON ai.AggregateID = cs.ItemID 
WHERE ai.ImNumber != me.CompanyNUmber 
AND me.ID IN ('value1','value2','value2','value3','value4','value5')'
ughai
  • 9,830
  • 3
  • 29
  • 47
anchor
  • 1,685
  • 2
  • 11
  • 11
  • is this for mysql or sql-server? – ughai May 05 '15 at 08:52
  • add more details about your question? will you have max 5 dynamic values? your question is not clear – ughai May 05 '15 at 09:06
  • I am using sqlserver 2008 and this is my dynamic query 'SET @SQLString = 'select me.EventID , ai.AggregateInfo, '''+@DateBegin+''' AS DateBegin, '''+@EDateEnd+''' AS DateEnd FROM tbl_MajorEarners me INNER JOIN tbl_currentearners e ON e.EventID = me.EventID INNER JOIN tbl_AggregateInfo ai ON ai.AggregateID = cs.ItemID WHERE ai.ImNumber != me.CompanyNUmber AND me.ID IN ('value1','value2','value2','value3','value4','value5')'' – anchor May 05 '15 at 09:15
  • My apologies I figured that I should place a plus sign in front of each value e.g (+value+ , +value+ , +value+) – anchor May 05 '15 at 09:20
  • 1
    @anchor Don't put additional info into comments. You can edit the question. – Ralf May 05 '15 at 09:24
  • 1
    if you have 5 values as defined in your query, why do you need dynamic sql? – ughai May 05 '15 at 09:54

1 Answers1

0

I think this is what you are looking for

SET @SQLString = 'select me.EventID , ai.AggregateInfo, ''@DateBegin'' AS DateBegin, ''@EDateEnd'' AS DateEnd 
FROM tbl_MajorEarners me 
INNER JOIN tbl_currentearners e ON e.EventID = me.EventID 
INNER JOIN tbl_AggregateInfo ai ON ai.AggregateID = cs.ItemID 
WHERE ai.ImNumber != me.CompanyNUmber 
AND me.ID IN (''value1'',''value2'',''value2'',''value3'',''value4'',''value5'')'

which sets @SqlString to

select me.EventID , ai.AggregateInfo, '@DateBegin' AS DateBegin, '@EDateEnd' AS DateEnd 
FROM tbl_MajorEarners me 
INNER JOIN tbl_currentearners e ON e.EventID = me.EventID 
INNER JOIN tbl_AggregateInfo ai ON ai.AggregateID = cs.ItemID 
WHERE ai.ImNumber != me.CompanyNUmber 
AND me.ID IN ('value1','value2','value2','value3','value4','value5')
3dd
  • 2,520
  • 13
  • 20