SQLTeam.com | Weblogs | Forums

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


#1

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


Search Product
#2

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