Hello,
RE AdventureWorks2012
Any advice on how to pass a list of sub category id's to an "IN" statement, with the caviat that the list may be null depending on the user selection from an MS Access list box.
Source where I did the most homework on this:
http://www.sommarskog.se/index.html
-->http://www.sommarskog.se/arrays-in-sql.html
-->http://www.sommarskog.se/arrays-in-sql-2008.html
*FYI the WHERE clause is broken currently
RE AdventureWorks2012
Any advice on how to pass a list of sub category id's to an "IN" statement, with the caviat that the list may be null depending on the user selection from an MS Access list box.
Source where I did the most homework on this:
http://www.sommarskog.se/index.html
-->http://www.sommarskog.se/arrays-in-sql.html
-->http://www.sommarskog.se/arrays-in-sql-2008.html
*FYI the WHERE clause is broken currently
Code:
use [AdventureWorks2012]
go
/*-------HOUSE KEEPING--------*/
drop table #SubCategoryUserSelection
go
drop table #CategoryUserSelection
go
/*---THIS WILL SIMULATE A USER SELECTION THAT FILLS A TEMP TABLE WITH ADODB CONNECTION----*/
select * into #SubCategoryUserSelection from
(
select distinct
ProductSubcategoryID
from Production.ProductSubcategory p
where ProductSubcategoryID in (1,2,3)
) x
go
select * into #CategoryUserSelection from
(
select distinct
ProductCategoryID
from Production.ProductSubcategory p
where ProductCategoryID in (1,2)
) x
go
/*-----RESULT WITH FILTER APPLIED-------*/
select
p.Name
,c1.ProductCategoryID
,c1.Name
,c1.ProductSubcategoryID
,c2.Name
--,p.*
from Production.Product p
left join Production.ProductSubcategory c1 on p.ProductSubcategoryID = c1.ProductSubcategoryID
left join Production.ProductCategory c2 on c1.ProductCategoryID = c2.ProductCategoryID
where (case when exists(select 1 from #SubCategoryUserSelection x where x.ProductSubcategoryID=c1.ProductSubcategoryID) then (select * from #SubCategoryUserSelection x where x.ProductSubcategoryID=c1.ProductSubcategoryID) else 0 end )
-- and
-- (exists(select 1 from #CategoryUserSelection x2 where x2.ProductCategoryID = c1.ProductCategoryID))