create proc teste(
@pArray int[] -- EXAMPLE
)
begin
select DISTINCT v.product_id,
v.name,
CAST( IsNull(p.description,'') AS VARCHAR(MAX)) as description,
0 as inventory,
IsNull(v.bName,'') ,
IsNull(v.i_thumb,'') ,
IsNull(v.i_small,''),
IsNull(v.i_normal,''),
IsNull(v.i_big,''),
product_type = (select IsNull(pt.name,'') from producttype pt where pt.producttype_id = v.producttype_id),
topSeller = (select pr.rank from product_rank pr where pr.product_id = v.product_id) ,
isRelease = (select (case v.fgLancamento when 1 then 'True' else 'False' end)) ,
IsNull(v.description,'') as caracteristics,
mainDept = 0
from v_product v
inner join product_variant pv ON v.product_id = pv.product_id
inner join product p on v.product_id = p.product_id
where v.active_flag = 1
and @pArray IN (select top 1 dl.dept_id
from dept_product dp
inner join deptLookup dl ON dl.child_id = dp.dept_id
where product_id = v.product_id)
order by product_id desc
end
