Using a variable in a WHERE clause with the IN operator

[Jon Strand]

The IN operator in SQL requires that the list you match against be contained in parentheses, like so:

SELECT *

FROM my_table

WHERE column_name IN ('value1', 'value2')

I'd like to provide the comma separated list of values using a variable.  But, if I include the parentheses in the variable, and try to evaluate the variable directly after IN, I get an error.

my_string_with_parens_in_it = "('value1', 'value2')"

WHERE column_name IN @{my_string_with_parens_in_it}

If we move the parentheses out of the variable, and they are instead part of the query (as seems right to me), the variable is not evaluated properly, and no records are found.

my_string_without_parens = "'value1', 'value2'"

WHERE column_name IN (@{my_string_without_parens})

Storing the value of the variable in an ADOR (and then generating a proof set) shows that it is being set correctly.  Copy/pasting that value directly into the query makes it work, so this seems like an XMPie issue.  Has anyone used a variable in this way? 

Thanks very much for your time!

Jon Strand

[Wayne]

Hi Jon,

Pretty sure this is not the way XMPie passes variables

You can pass each variable separately  - WHERE column_name IN (@{var1},@{var2}) 

Regards,

Wayne

[Tom Gooding]

I don't think SQL allows using a variable in an IN clause like that...

DECLARE @Countries VARCHAR(250)
SET @Countries = '''France'', ''Germany'''

/* Basic query obviously works */
SELECT * FROM Country WHERE Name IN ('France', 'Germany');

/* Trying to use a variable in the IN clause doesn't work
 despite looking identical to the basic query when printed */
SELECT *
FROM Country
WHERE Name IN (@Countries);
PRINT 'SELECT * FROM Country WHERE Name IN (' + @Countries + ');'

/* Possible workaround using dynamic SQL */
DECLARE @SQL VARCHAR(250)
SET @SQL = 'SELECT * FROM Country WHERE Name IN (' + @Countries + ');'
EXEC (@SQL)

[Jon Strand]

Aha!  Thank you both. I had assumed XMPie would evaluate its expressions and variables first, and then execute the resulting string as a new query.  But it seems to rebuild the query, mapping its variables directly onto SQL variables.  Oof, bad news for me.  I'll explore the dynamic SQL avenue, thanks for the suggestion. If anyone has any advice about that, it would be much appreciated.  Otherwise, I'll report back here with what I've found.

Thanks!