Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using In Statement in Where Clause Filtering

Status
Not open for further replies.

devRyan

Programmer
Sep 1, 2006
104
US
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:
Code:
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?

Code:
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

 
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
 
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
 
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top