SQLTeam.com | Weblogs | Forums

Search product by multiple select category, MULTIPLE COLOR, MULTIPLE BRAND for e-comm project just like Jabong

How to make proc in sql server ???
Plz help me ,,,

please provide sample data. here is one approach. now knowing your data hard to provide a clean solution

create proc products_sp
as
(
	@categories xml,
	@brands xml,
	@colors xml,
	@prices xml
)
begin
	/*
		@category xml

		<Categories>   
		  <Category>
			<CategoryId>1</CategoryId>			
		  </Category> 
		  <Category>
			<CategoryId>2</CategoryId>    
		  </Category> 
		</Categories>
	*/
	create table #categories(categoryid int not null);
	create table #brands(brandid int not null);
	create table #colors(colorid int not null);
	create table #prices(priceid int not null);

	insert into #categories
	select  T.C.value('(CategoryId/text())[1]','varchar(500)') as CategoryId
      from @categories.nodes('/Categories/Category') as T(C) 

	select 
	  from dbo.products p
	  join dbo.brands b on p.Brandid = b.brandid
	  join dbo.ProductColors pc on pc.productId = p.Productid
	  join dbo.DimColors dc on pc.Colorid = = dc.Colorid
	  join dbo.ProductPrices pp on pp.productId = p.productId
	  join dbo.ProductCategories pcg on pcg.productId = p.productId
	  
	  left join #categories c on pcg.categoryid = c.categoryid
	  left join #brands b on b.Brandid = p.brandid

end