Using the IN operator with a variable

Question

Post reply

Using the IN operator with a variable

Elizabeth Balch

SSC Veteran

Points: 277
More actions
May 31, 2005 at 3:34 pm

#67089

I'm trying to understand how to pass a list of comma-separate values into my WHERE clause for use with an IN operator. Here's what it looks like, though no rows are being returned:
DECLARE @List varchar(100)
SET @List = 'ONA', 'TSV', 'KVS'
SELECT *
FROM table
WHERE column IN(@List)
I've also tried setting up my @List variable like this to deal with the commas:
SET @List = 'ONA' + '','' + 'TSV' + '','' + 'KVS'
However, I don't have any problems returning the appropriate data if I substitute the actual values in for my parameter like so:
SELECT *
FROM table
WHERE column IN('ONA', 'TSV', 'KVS')
OR
SELECT *
FROM table
WHERE column IN('ONA' + '','' + 'TSV' + '','' + 'KVS')
Any ideas would be appreciated!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

bdohmen SSCrazy Points: 2996 More actions · Answer 1

Try

DECLARE @List varchar(100)

SET @List = '''ONA'', ''TSV'', ''KVS'''

Also, you will need to execute the entire statement as dynamic SQL.

SELECT @List='SELECT * FROM Table WHERE Column IN ('+@List+')'

SP_EXECUTESQL (@List)

Brian

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

Solution is good. I would like to do it another way.

Please note Dynamic SQL will not work when the user has execute rights on a stored procedure and no select rights on a table.

Another way split the parameter and insert them into a variable of table data type or a temp table. Then use that in the main query.

Declare @params Table (Param VARCHAR (25))

/* split the varchar parameter and insert into the table dynamically */

Insert into @params VALUES ('ONA')

Insert into @params VALUES ('TSV')

Insert into @params VALUES ('KVS')

SELECT * FROM TABLE

JOIN

@Params A

ON

A.Param = Table.Column

I am not sure how IN operator will affect the performance in this case. In general it is slow.

Regards,
gova

philcart SSC-Forever Points: 47796 More actions · Answer 3

Use a function the returns a table to split the values and use the variable in the from clause.

There are a couple of example scripts in the script library. If you still have problems I can send you the one that we use which is really quick and doesn't use any looping.

--------------------
Colt 45 - the original point and click interface

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=182965#bm183028

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=179284#bm179295

**ASCII stupid question, get a stupid ANSI !!!**

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

http://www.sommarskog.se/arrays-in-sql.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

philcart SSC-Forever Points: 47796 More actions · Answer 6

Hey Frank, do you have a function key setup for that reply yet? It's amazing how many times it gets posted and still no-one searches before posting.

--------------------
Colt 45 - the original point and click interface

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

It's stored as favorite. Just because it is that frequently needed.

...no-one searches before posting...

Sad, but true.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 8

I've been thinking about the features for a perfect discussion forum recently. One of them would be an FAQ that is shown on the Create New Thread page, containing questions and answers such as this one.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

I don't really think this will help very much.

But a clean structured FAQ section surely will

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

philcart SSC-Forever Points: 47796 More actions · Answer 10

Just to hijack the thread a bit further

One thing I seen that sort of worked, was to make submitting a new post a three step process.

1) Enter the details for you post

2) based on the details entered, list most likely solutions to be relevant

3) None of the listed solutions helped, continue and submit post.

--------------------
Colt 45 - the original point and click interface

Elizabeth Balch SSC Veteran Points: 277 More actions · Answer 11

Thanks for all the advice, I'll just use a sub-select instead of passing in a variable to my IN operator or try the splitting method mentioned (though that may be an overkill for what I'm trying to accomplish).

BTW, I did search the site before posting. I searched for IN operator and also IN operator variables.

SJTerrill SSCrazy Points: 2938 More actions · Answer 12

Another, perhaps cheesy bit of ad-hoc-ery works:

DECLARE @TheseValues varchar(100)

SET @TheseValues = 'VAL1,VAL2,VAL3'

SELECT <ColumnNames>

FROM <TableName>

WHERE PatIndex('%' + <ColumnName> + '%',@TheseValues) > 0

My $0.02. The commas aren't really required in this case, but serve to make it more readable for me.