I have a test webpage where you search and filter products from adventureworks2012.
Im currently working/testing this with sql server 2012, but the real case is sql server 2005.
Since I need to filter out products from other tables I have to use outer joins to make sure all products are available for filtering.
And I also need to make sure the user can choose how to order by the result.
In this example I have joined two tables, and I only have a couple of search criterias, but it can be more tables and columns to filter by.
In the result set, I dont want to have duplicate products, so therefore I use group by on productId.
This sql code worked fine until I added the dynamic order by.
For every column I'd like to let the user order by, I have to add it to the group by, So now I have multiple products in my result set.
Change from group by ProductId to select distinct ProductId, might work.
But the column that is not chosen, will return null values, that is not good if I have 10+ different columns to order by.
The two varaibles @safetystocklevel and @vendorId are two examples where the user is filtering.
This code return to many products:
declare @sortType nvarchar(50)
set @sortType = 'ProductId'
declare @safetystocklevel int
set @safetystocklevel = 500
declare @vendorId int
set @vendorId = 1494
CASE WHEN @sortType = 'Status' THEN poh.Status END as Status,
CASE WHEN @sortType = 'ProductId' THEN p.ProductID END as ProductId
from Production.Product p
left outer join Purchasing.PurchaseOrderDetail pod on p.ProductID = pod.ProductID
left outer join Purchasing.PurchaseOrderHeader poh on poh.PurchaseOrderID = pod.PurchaseOrderID
left outer join Production.ProductDocument ppd on ppd.ProductID = p.ProductID
left outer join Production.Document pd on pd.DocumentNode = ppd.DocumentNode
(@safetystocklevel = '' or p.SafetyStockLevel = @safetystocklevel)
and (@vendorId = '' or poh.VendorID = @vendorId)
group by p.ProductID, poh.Status
CASE WHEN @sortType = 'Status' THEN poh.Status END ASC,
CASE WHEN @sortType = 'ProductId' THEN p.ProductID END ASC
What do you think?