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:
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?
Thanks
devRyan
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