WHERE variable(comma-separated list) IN (Array) 1

[SgtJarrow]

I have a query that returns a set of records in about 3 seconds. This is acceptable based on my requirements. But I am having to do some further filtering on the UI side that I would like to do in SQL instead which can be lengthy depending on the result set. I believe the overall length of the process can be greatly reduced if I can do the filtering in SQL as the filter would reduce the total join checks, etc.

I am not sure if it is possible...hoping someone can give me some guidance. The query requires a varchar(1000) variable that is a comma-separated list of values. This needs to be compared against an IN list...If one or more of the passed values matches any of the array of values, the record should be returned. See below for examples and partial script.

Thanks for pointing me in the right direction or letting me know if this is even possible...

DECLARE @ProductGroups VARCHAR(1000)

[red]SET @ProductGroups = 'Dogfish Head - BOTTLE,Dogfish Head - DRAFT'[/red]

SELECT DISTINCT 
	c.RecKey
	, c.RecName
	, cag.Address 
	, cag.City 
	, cag.State
	, cag.Zip 
	, c.Phone 
	, cag.Latitude 
	, cag.Longitude 
	, c.ClassOfTradeDesc 
	, c.RegionName
	, c.ProductList
FROM CORPDBNEW.SchenckDW.dbo.dwCustomers c 
INNER JOIN BeerTracker.CustomerAddressAndGeoCordinates cag ON CAST(c.RecKey AS VARCHAR(50)) = cag.RecKey
WHERE c.ActiveFlag = 1 
	AND c.IsNonRetail = 0
        [red](AND ANY FROM @ProductGroups) IN (SELECT ITEM FROM dbo.Split(c.ProductList))[/red]
ORDER BY c.RecName


Select results without filter:
RecKey     ProductList
1          Dogfish Head - BOTTLE,Dogfish Head - DRAFT
2          Miller - BOTTLE,Miller - DRAFT
3          Dogfish Head - BOTTLE,Dogfish Head - DRAFT,
           Miller - BOTTLE,Miller - DRAFT
4          Dogfish Head - BOTTLE,Dogfish Head - DRAFT
5          Miller - BOTTLE,Miller - DRAFT

Select results wanted with filter:
RecKey     ProductList
1          Dogfish Head - BOTTLE,Dogfish Head - DRAFT
3          Dogfish Head - BOTTLE,Dogfish Head - DRAFT,
           Miller - BOTTLE,Miller - DRAFT
4          Dogfish Head - BOTTLE,Dogfish Head - DRAFT

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[RiverGuy]

Yes you can do this. Do a Google search for "SQL Server Split Function." There are lots of examples out there where you basically create a table-valued function. The function takes in your delimited list of values and outputs a table variable with rows for the individual values. You can join this table to your other tables, or use the IN clause.

 

[SgtJarrow]

Thanks for the split reference. I am already using that in my query. The issue was on the front side of the WHERE clause...before the IN.

But after I posted I found a solution that worked anyway...This is all fairly static data, so I built a table to house the information and just have to do basic select statements against it to get the results I need. It takes about 45 seconds each night to populate the table, but the queries now complete in under 1 second. That is a good trade off for me.

Thanks for reading though...You are definitely one of the few I knew could give me an answer...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer