Using a string of quoted values in a variable for a SQL WHERE CLAUSE

Question

The answer escapes me...maybe because it is not possible...

Example that works...

SELECT * FROM TABLEA WHERE FIELD1 IN ('aaa','bbb','ccc')

Example that does not work...

Attempt to leverage variable so that I can define the values once in a string of statements

DECLARE @ListValues VARCHAR(50)

SET @ListValues = '''aaa'',''bbb'',''ccc'''

SELECT * FROM TABLEA WHERE FIELD1 IN (@ListValues)

This is is obviously only a small part of the equation and for other reasons...

I cannot leverage a table for the values and change this to a true sub-query

The closest question I could find was this one... but does not cover my requirements obviously... Storing single quotes in varchar variable SQL Server 2008

Thanks in advance.

Thanks for the reformat marc_s, i saw the result of post and was in the process of doing the same...checking out answers... — chose, May 20 '14 at 21:34
Asked [so many times here](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause). I think conceptually I'd prefer the table-value-function version. — Clockwork-Muse, May 20 '14 at 21:46

score 1 · Answer 1 · answered May 20 '14 at 21:25

1

You can do this using dynamic SQL:

DECLARE @ListValues VARCHAR(MAX)
       ,@SQL VARCHAR(MAX)
SELECT @ListValues = '''aaa'',''bbb'',''ccc'''
      ,@SQL = 'SELECT * FROM TABLEA WHERE FIELD1 IN ('+@ListValues+')'
EXEC (@SQL)

answered May 20 '14 at 21:25

Hart CO

34,064
6
48
63

score 0 · Accepted Answer · answered May 20 '14 at 21:26

It doesn't work because the IN operator expects a list of items - here strings.

What you're supplying with your @ListValues variable however is a single string - not a list of strings.

What you could do is use a table variable and store your values in it:

DECLARE @ListOfValues TABLE (ItemName VARCHAR(50))

INSERT INTO @ListOfValues(ItemName) 
VALUES('aaa'), ('bbb'), ('ccc')

SELECT *    
FROM TABLEA 
WHERE FIELD1 IN (SELECT ItemName FROM @ListOfValues)

peter.petrov · Answer 3 · 2014-05-20T21:31:59.393

0

Build your whole SQL query dynamically (say it's stored in a string variable @sql),
and then execute it with EXEC (@sql). Better yet, use the sp_executesql SP
because this approach is more secure (less prone to SQL injection) than EXEC.

See: sp_executesql

edited May 20 '14 at 21:31

answered May 20 '14 at 21:26

peter.petrov

38,363
16
94
159

Serpiton · Answer 4 · 2014-05-20T21:36:16.697

The IN operator in SQLServer expect a list of values, your variable is a single string, the query parsed will be different

SELECT * FROM TABLEA WHERE FIELD1 IN ('aaa','bbb','ccc')
SELECT * FROM TABLEA WHERE FIELD1 IN ("'aaa','bbb','ccc'")

Attention: the double quotes are there only for readability, to get the string with single quote in it.

if you know a programming language the first one is like searching in an array, the second is a string.

To store a list in your variable it need to a table

DECLARE @varTable TABLE (field1 varchar())

So that you can use it in your IN

SELECT * FROM TABLEA WHERE FIELD1 IN (SELECT field1 FROM @varTable)

To add values to the table variable use an INSERT statament like usual for tables.

Using a string of quoted values in a variable for a SQL WHERE CLAUSE

4 Answers4