amending a stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

amending a stored procedure

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-04-01 : 06:13:29

Hi

I have this stored procedure below that calls related products into an xml package. I want to also display the product price from a table called ProductVariant. The column name is Price and SalePrice.

I've tried to alter the SQL to join pv.Price and pv.SalePrice but nothing works. Anybody have an idea?

ALTER PROCEDURE [dbo].[aspdnsf_GetCustomersRelatedProducts_with_variantid]
@CustomerViewID nvarchar(50),
@ProductID int,
@CustomerLevelID int,
@InvFilter int,
@affiliateID int,
@storeID int = 1,
@filterProduct bit = 0

AS
SET NOCOUNT ON

DECLARE
@custlevelcount int,
@CustomerLevelFilteringIsAscending bit,
@FilterProductsByCustomerLevel tinyint,
@relatedprods varchar(8000),
@DynamicProductsEnabled varchar(10),
@DynamicProductsDisplayed int,
@FilterProductsByAffiliate tinyint,
@affiliatecount int,
@AffiliateExists int

SELECT @custlevelcount = si.rows from dbo.sysobjects so with (nolock) join dbo.sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductCustomerLevel') and si.indid < 2 and type = 'u'
SELECT @FilterProductsByCustomerLevel = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByCustomerLevel'
SELECT @FilterProductsByAffiliate = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByAffiliate'
SELECT @affiliatecount = count(*), @AffiliateExists = sum(case when AffiliateID = @affiliateID then 1 else 0 end) from dbo.ProductAffiliate with (nolock) where ProductID = @ProductID

SET @CustomerLevelFilteringIsAscending = 0
SELECT @CustomerLevelFilteringIsAscending = case configvalue when 'true' then 1 else 0 end
FROM dbo.appConfig with (nolock)
WHERE name like 'FilterByCustomerLevelIsAscending'

SELECT @DynamicProductsDisplayed = CAST(ConfigValue AS INT) from AppConfig with (NOLOCK) where Name = 'DynamicRelatedProducts.NumberDisplayed'
SELECT @DynamicProductsEnabled = CASE ConfigValue WHEN 'true' then 1 else 0 end from AppConfig with (NOLOCK) where Name = 'DynamicRelatedProducts.Enabled'
select @relatedprods = replace(cast(relatedproducts as varchar(8000)), ' ', '') from dbo.product with (NOLOCK) where productid = @productid

IF(@DynamicProductsEnabled = 1 and @DynamicProductsDisplayed > 0)
BEGIN
SELECT TOP (@DynamicProductsDisplayed) tp.ProductID, tp.ProductGUID, tp.ImageFilenameOverride, tp.SKU, tp.SEAltText, tp.Name, tp.Description FROM Product tp with (NOLOCK) JOIN
(
SELECT p.ProductID, p.ProductGUID, p.ImageFilenameOverride, p.SKU, p.SEAltText, p.Name, p.Description, pv.Price
from dbo.product p with (nolock)
join dbo.split(@relatedprods, ',') rp on p.productid = cast(rp.items as int)
left join dbo.productcustomerlevel pcl with (nolock) on p.productid = pcl.productid and @FilterProductsByCustomerLevel = 1
join (select p.ProductID
from dbo.product p with (nolock)
join dbo.split(@relatedprods, ',') rp on p.productid = cast(rp.items as int)
join (select ProductID, sum(Inventory) Inventory from dbo.productvariant with (nolock) group by ProductID) pv on p.ProductID = pv.ProductID
left join (select ProductID, sum(quan) inventory from dbo.inventory i1 with (nolock) join dbo.productvariant pv1 with (nolock) on pv1.variantid = i1.variantid join dbo.split(@relatedprods, ',') rp1 on pv1.productid = cast(rp1.items as int) group by pv1.productid) i on i.productid = p.productid
where case p.TrackInventoryBySizeAndColor when 1 then isnull(i.inventory, 0) else pv.inventory end >= @InvFilter
) tp on p.productid = tp.productid
where published = 1 and deleted = 0 and p.productid != @productid
and GETDATE() BETWEEN ISNULL(AvailableStartDate, '1/1/1900') AND ISNULL(AvailableStopDate, '1/1/2999')
and case
when @FilterProductsByCustomerLevel = 0 then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID or pcl.CustomerLevelID is null then 1
when @CustomerLevelID=0 and pcl.CustomerLevelID is null then 1
when @CustomerLevelID is null or @custlevelcount = 0 then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
UNION ALL
SELECT pr.ProductID, pr.ProductGUID, pr.ImageFilenameOverride, pr.SKU, pr.SEAltText, pr.Name, pr.Description
FROM Product pr WITH (NOLOCK)
WHERE pr.ProductID in (
SELECT TOP 100 PERCENT p.ProductID FROM Product p with (NOLOCK)
JOIN
(
SELECT ProductID FROM ProductView with (NOLOCK) WHERE CustomerViewID
IN
(
SELECT CustomerViewID FROM ProductView with (NOLOCK)
WHERE ProductID = @ProductID AND CustomerViewID <> @CustomerViewID
)
AND ProductID <> @ProductID
AND ProductID NOT
IN
(
select ProductID
from product with (NOLOCK)
join split(@relatedprods, ',') rp on productid = cast(rp.items as int)
group by ProductID
)
) a on p.ProductID = a.ProductID

LEFT JOIN dbo.productcustomerlevel pcl with (NOLOCK) on p.productid = pcl.productid and @FilterProductsByCustomerLevel = 1
left join dbo.ProductAffiliate pa with (nolock) on p.ProductID = pa.ProductID
WHERE
Published = 1 AND Deleted = 0
and GETDATE() BETWEEN ISNULL(AvailableStartDate, '1/1/1900') AND ISNULL(AvailableStopDate, '1/1/2999')
and case
when @FilterProductsByCustomerLevel = 0 then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID or pcl.CustomerLevelID is null then 1
when @CustomerLevelID=0 and pcl.CustomerLevelID is null then 1
when @CustomerLevelID is null or @custlevelcount = 0 then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0)
group by p.ProductID
order by COUNT(*) desc
)
)prd on tp.ProductID = prd.ProductID
inner join (select distinct a.ProductID from Product a with (nolock) left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) ps on tp.ProductID = ps.ProductID
END

IF(@DynamicProductsEnabled = 0 and @DynamicProductsDisplayed > 0)
BEGIN
select TOP (@DynamicProductsDisplayed) p.ProductID, p.ProductGUID, p.ImageFilenameOverride, p.SKU, p.sename,p.SEAltText, p.Name, p.Description, VariantID, Price, SalePrice
from dbo.product p with (nolock)
join dbo.split(@relatedprods, ',') rp on p.productid = cast(rp.items as int)
left join dbo.PRoductVariant pv with (nolock) on pv.productid = p.productid and pv.Deleted=0 and pv.Published = 1 and pv.IsDefault = 1
left join dbo.productcustomerlevel pcl with (nolock) on p.productid = pcl.productid and @FilterProductsByCustomerLevel = 1
join (select p.ProductID
from dbo.product p with (nolock)
join dbo.split(@relatedprods, ',') rp on p.productid = cast(rp.items as int)
join (select ProductID, sum(Inventory) Inventory from dbo.productvariant with (nolock) group by ProductID) pv on p.ProductID = pv.ProductID
left join (select ProductID, sum(quan) inventory from dbo.inventory i1 with (nolock) join dbo.productvariant pv1 with (nolock) on pv1.variantid = i1.variantid join dbo.split(@relatedprods, ',') rp1 on pv1.productid = cast(rp1.items as int) group by pv1.productid) i on i.productid = p.productid
where case p.TrackInventoryBySizeAndColor when 1 then isnull(i.inventory, 0) else pv.inventory end >= @InvFilter
) tp on p.productid = tp.productid
inner join (select distinct a.ProductID from Product a with (nolock) left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) ps on p.ProductID = ps.ProductID

where
p.published = 1 and p.deleted = 0 and p.productid != @productid
and GETDATE() BETWEEN ISNULL(AvailableStartDate, '1/1/1900') AND ISNULL(AvailableStopDate, '1/1/2999')
and case
when @FilterProductsByCustomerLevel = 0 then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID or pcl.CustomerLevelID is null then 1
when @CustomerLevelID=0 and pcl.CustomerLevelID is null then 1
when @CustomerLevelID is null or @custlevelcount = 0 then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources