Using In Statement in Where Clause Filtering

[devRyan]

Hi All,

I had a thread last year asking about how to do some more advanced filtering using the WHERE clause, as seen here: thread183-1366770.

The result was the code here:

Declare @Temp Table(UserName VarCHar(100), EyeColor VarChar(100), ShoeSize int)

Insert Into @Temp Values('devRyan', 'blue', 10)
Insert Into @Temp Values('AlexCuse', null, 10)
Insert Into @Temp Values('bborissov', 'blue', NULL)
Insert Into @Temp Values('gmmastros', 'red', 9)
Insert Into @Temp Values('SQLBill', '', 8)

Declare @UserName VarChar(100)
Declare @EyeColor VarChar(100)
Declare @ShoeSize Int

-- Try changing the values here.
Set @UserName = ''
Set @EyeColor = ''
Set @ShoeSize = 0

Select *
From   @Temp
Where  (@UserName = '' Or UserName = @UserName)
       And (@EyeColor = '' Or EyeColor = @EyeColor)
       And (@ShoeSize = 0 Or ShoeSize = @ShoeSize)

This has worked quite well, but now I am required to expand the functionality of my search query to include multiselect fields, and am having some trouble getting the query to work with the new requirements. I'm hoping someone here can help.

So far, I've tried to the individual claused to use an IN statement, and send a comma delimited list, as below, but that doesn't seem to work. Any ideas on how to make this method work with list strings?

Declare @Temp Table(UserName VarCHar(100), EyeColor VarChar(100), ShoeSize int)

Insert Into @Temp Values('devRyan', 'blue', 10)
Insert Into @Temp Values('AlexCuse', null, 10)
Insert Into @Temp Values('bborissov', 'blue', NULL)
Insert Into @Temp Values('gmmastros', 'red', 9)
Insert Into @Temp Values('SQLBill', '', 8)

Declare @UserName VarChar(100)
Declare @EyeColor VarChar(100)
Declare @ShoeSize Int

-- Try changing the values here.
Set @UserName = 'devRyan','AlexCuse'
Set @EyeColor = ''
Set @ShoeSize = 0

Select *
From   @Temp
Where  (@UserName = '' Or UserName IN (@UserName))
       And (@EyeColor = '' Or EyeColor = @EyeColor)
       And (@ShoeSize = 0 Or ShoeSize = @ShoeSize)

Thanks

devRyan

 

[SQLSister]

There are three FAQs on passing a comma delimited list to a stored proc. Suggest you read them and come back if they don;t solve your problem

"NOTHING is more important in a database than integrity." ESquared

 

[devRyan]

Thanks, will do.

 

[devRyan]

I just wanted to report back that I found what I needed in faq183-5207, Option 4.

Thanks for the guidance SQLSister, and excellent write-up ESqared.

Ryan

 

[ESquared]

Thanks. Don't miss part III if you need to deal with longer lists or if you want a faster function.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]