Where IN @txtVariable (where txtVariable is a CSV list)

Question

Where IN @txtVariable (where txtVariable is a CSV list)

Kevlarmpowered

Say Hey Kid

Points: 713
More actions
August 19, 2015 at 8:28 am

#398924

I must be overlooking something...
I am trying to do something like
declare @txtSearchMajors nvarchar(500) = 'U101,U304,P748,I333,O948'
select *
from tblMajors
where txtMajorID IN (@txtSearchMajors)
I can't seem to get this to work. There must be something simple I am missing to this.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You're comparing against a single value that have commas in it, not separate values.

To achieve what you want, you need a splitter. It can be found in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

And you could use it like this:

declare @txtSearchMajors nvarchar(500) = 'U101,U304,P748,I333,O948'

select *

from tblMajors

where txtMajorID IN (SELECT Item FROM dbo.DelimitedSplitN4K(@txtSearchMajors, ','))

--OR

select *

from tblMajors m

JOIN dbo.DelimitedSplitN4K(@txtSearchMajors, ',') s ON m.txtMajorID = s.Item

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 2

Probably the best way here, taking the appropriate precautions against SQL Injection, would be a lick of dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kevlarmpowered Say Hey Kid Points: 713 More actions · Answer 3

My DBAs frown on dynamic SQL... so I think I will go with the splitter function. Which there was way to say treat this as a CSV rather than a value with commas easier.

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 4

Kevlarmpowered (8/19/2015)
My DBAs frown on dynamic SQL...

They REALLY need to get over that. Properly written dynamic SQL is SQL Injection proof, safe, fast, and will cache an execution plan just like any other SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)