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