passing parameters to filter using "IN" keyword

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm trying to build a stored procedure to run in a SQL 2000 environment.

I'd like to search for invoices by invoice type. The user interface for
invoice type has a checked listbox where multiple selections are allowed.

WHERE SS.RouteGroup IN (@RouteGroup)

If I pass a single selection to the procedure using the following statement,
I get the correct list of pest control invoices.

exec ske_getrimms @DateStart = '1/24/2005', @DateEnd = '1/24/2005', @Branch
= 'Boise', @RouteGroup = 'Pest Control'

However, I don't get any results if I also try to filter for multiple route
groups as follows.

exec ske_getrimms @DateStart = '1/24/2005', @DateEnd = '1/24/2005', @Branch
= 'Boise', @RouteGroup = 'Pest Control, Irrigation, Mowing'

I can't seem to find a way of passing multiple selections to the stored
procedure through a parameter. Am I missing something here?

Thanks,

Andre Ranieri
 
It's a FAQ. See the archives (google groups) unless you're worried that the
NSA will be watching.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I was afraid it might be a FAQ - I tried looking it up on Google first but
ran into problems searching using the keyword "IN", Google likes to drop
those.
 
Google drops 'in' just like it drops 'the', frequently used and has nothing
to do with the search normally. Not because of sql injection attacks.
If you want google to include those you can put quotes around it like "in
the water" and it will look for that exact phrase.

now, on to the real question. You can solve this problem.
http://www.sommarskog.se/arrays-in-sql.html
I use this solution when required and it works great.

Wayne
 
Thanks Otis and Wayne for your help. For anyone reading this down the road,
the Google keyword that helped find the solution for me was "array" for the
comma delineated filter list in client T-SQL.

I ended up using one of the solutions in Wayne's link - I created a #temp
helper table and iterated through the comma delineated list to insert each
value as a record in that table. In the stored procedure, all I had to do
was add a subquery for WHERE Result IN(SELECT Value FROM #tmp)

Cheers,

Andre Ranieri
 
Back
Top