Trying to amend a stored procedure

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 Administration

Trying to amend a stored procedure

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-02-13 : 04:21:51

Hi

I am trying to amend a stored procedure.

This code executes a script within the stored procedure that allows filtering by product name A-Z and Z-A

-- Name Z-A
ELSE IF @sortEntity = 2001 and @sortEntityName = 'category' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductCategory pc with (nolock) on pc.CategoryID = @categoryID and pc.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Name desc
END
ELSE IF @sortEntity = 2001 and @sortEntityName = 'manufacturer' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductManufacturer pm with (nolock) on pm.ManufacturerID = @manufacturerID and pm.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Name desc
END

I want to try to add the option so that I can also sort/filter by manufacturer name. Each product has a Product ID in the product table and within another table (Manufacturer) this ID is linked to a manufacturer ID.

The code I have added below is not working as it says the column name does not exist. Any ideas on where I am going wrong?

-- Manufacturer A-Z
ELSE IF @sortEntity = 2002 and @sortEntityName = 'category' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductCategory pc with (nolock) on pc.CategoryID = @categoryID and pc.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Manufacturer
END
ELSE IF @sortEntity = 2002 and @sortEntityName = 'manufacturer' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductManufacturer pm with (nolock) on pm.ManufacturerID = @manufacturerID and pm.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Manufacturer
END
-- Manufacturer Z-A
ELSE IF @sortEntity = 2003 and @sortEntityName = 'category' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductCategory pc with (nolock) on pc.CategoryID = @categoryID and pc.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Manufacturer
END
ELSE IF @sortEntity = 2003 and @sortEntityName = 'manufacturer' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductManufacturer pm with (nolock) on pm.ManufacturerID = @manufacturerID and pm.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Manufacturer
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 05:06:36

i think its part in order by

order by p.Manufacturer
if my assumption is right,Manufacturer is in ProductManufacturer table so you should be using alias pm instead of p
Also whats the purpose of using order by in select which inserts data to table? order by will not guarantee that records are populated to table in any predefined order when used in INSERT ...SELECT so it doesnt make any sense to use order by in above case. Order BY has no relevance in INSERTs unless you've TOP clause specified in select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-02-13 : 05:38:11

Ok that seemed to overcome the Stored Procedure not executing but when the filter is changed to Sort by Manufactuer A-Z the order does not change.

A link is here to explain http://109.108.155.82/c-916-b-vitamins.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 05:45:45

you should be adding the order by to select part which select data from final table, not where you populate it
That was my point. You'll still need order by if you want final results to be in specific order

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-02-20 : 07:22:09

Hi

I am not that proficient in SQL and am just trying to amend this stored procedure that was written by another developer.

So far I have the code as below and the procedure is executing ok but when you try to sort the items by manufacturer it is not displying them in order.

-- Manufacturer A-Z
ELSE IF @sortEntity = 2002 and @sortEntityName = 'category' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductCategory pc with (nolock) on pc.CategoryID = @categoryID and pc.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Name desc
END
ELSE IF @sortEntity = 2002 and @sortEntityName = 'manufacturer' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductManufacturer pm with (nolock) on pm.ManufacturerID =
@manufacturerID and pm.ProductID = p.ProductID
join Manufacturer m with (nolock) on m.ManufacturerID =
pm.ManufacturerID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order
by m.Name
END

-- Manufacturer Z-A

ELSE IF @sortEntity = 2003 and @sortEntityName = 'category' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductCategory pc with (nolock) on pc.CategoryID = @categoryID and pc.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Name desc
END
ELSE IF @sortEntity = 2003 and @sortEntityName = 'manufacturer' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductManufacturer pm with (nolock) on pm.ManufacturerID =
@manufacturerID and pm.ProductID = p.ProductID
join Manufacturer m with (nolock) on m.ManufacturerID =
pm.ManufacturerID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order
by m.Name
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 08:04:25

quote:
Originally posted by deanglen

Hi

I am not that proficient in SQL and am just trying to amend this stored procedure that was written by another developer.

So far I have the code as below and the procedure is executing ok but when you try to sort the items by manufacturer it is not displying them in order.

-- Manufacturer A-Z
ELSE IF @sortEntity = 2002 and @sortEntityName = 'category' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductCategory pc with (nolock) on pc.CategoryID = @categoryID and pc.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Name desc
END
ELSE IF @sortEntity = 2002 and @sortEntityName = 'manufacturer' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductManufacturer pm with (nolock) on pm.ManufacturerID =
@manufacturerID and pm.ProductID = p.ProductID
join Manufacturer m with (nolock) on m.ManufacturerID =
pm.ManufacturerID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1
order by m.Name
END

-- Manufacturer Z-A

ELSE IF @sortEntity = 2003 and @sortEntityName = 'category' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductCategory pc with (nolock) on pc.CategoryID = @categoryID and pc.ProductID = p.ProductID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1 order by p.Name desc
END
ELSE IF @sortEntity = 2003 and @sortEntityName = 'manufacturer' BEGIN
INSERT #displayorder select p.ProductID
from Product p with (nolock)
join ProductVariant pv with (nolock) on p.ProductId = pv.ProductId
join ProductManufacturer pm with (nolock) on pm.ManufacturerID =
@manufacturerID and pm.ProductID = p.ProductID
join Manufacturer m with (nolock) on m.ManufacturerID =
pm.ManufacturerID
where p.Published = 1 and p.Deleted = 0 and pv.IsDefault = 1
order by m.Name
END

Again restating
The code in blue above WILL NOT ensure that you get results ordered by manufacture name

For that you need to add a ORDER BY Name in select statement that selects from your final table (and you've not shown us the select part yet!)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-02-20 : 08:14:51

SELECT
p.ProductID,
p.Name,
pv.VariantID,
pv.Name AS VariantName,
p.ProductGUID,
p.Summary,
p.Description,
p.SEKeywords,
p.SEDescription,
p.SpecTitle,
p.MiscText,
p.SwatchImageMap,
p.IsFeaturedTeaser,
p.FroogleDescription,
p.SETitle,
p.SENoScript,
p.SEAltText,
p.SizeOptionPrompt,
p.ColorOptionPrompt,
p.TextOptionPrompt,
p.ProductTypeID,
p.TaxClassID,
p.SKU,
p.ManufacturerPartNumber,
p.SalesPromptID,
p.SpecCall,
p.SpecsInline,
p.IsFeatured,
p.XmlPackage,
p.ColWidth,
p.Published,
p.RequiresRegistration,
p.Looks,
p.Notes,
p.QuantityDiscountID,
p.RelatedProducts,
p.UpsellProducts,
p.UpsellProductDiscountPercentage,
p.RelatedDocuments,
p.TrackInventoryBySizeAndColor,
p.TrackInventoryBySize,
p.TrackInventoryByColor,
p.IsAKit,
p.ShowInProductBrowser,
p.IsAPack,
p.PackSize,
p.ShowBuyButton,
p.RequiresProducts,
p.HidePriceUntilCart,
p.IsCalltoOrder,
p.ExcludeFromPriceFeeds,
p.RequiresTextOption,
p.TextOptionMaxLength,
p.SEName,
p.Deleted,
p.CreatedOn,
p.ImageFileNameOverride,
pv.VariantGUID,
pv.Description AS VariantDescription,
pv.SEKeywords AS VariantSEKeywords,
pv.SEDescription AS VariantSEDescription,
pv.Colors,
pv.ColorSKUModifiers,
pv.Sizes,
pv.SizeSKUModifiers,
pv.FroogleDescription AS VariantFroogleDescription,
pv.SKUSuffix,
pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,
pv.Price,
pv.CustomerEntersPrice,
pv.CustomerEntersPricePrompt,
isnull(pv.SalePrice, 0) SalePrice,
cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,
pv.MSRP,
pv.Cost,
isnull(pv.Points,0) Points,
pv.Dimensions,
case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,
pv.DisplayOrder as VariantDisplayOrder,
pv.Notes AS VariantNotes,
pv.IsTaxable,
pv.IsShipSeparately,
pv.IsDownload,
pv.DownloadLocation,
pv.Published AS VariantPublished,
pv.IsSecureAttachment,
pv.IsRecurring,
pv.RecurringInterval,
pv.RecurringIntervalType,
pv.SubscriptionInterval,
pv.SEName AS VariantSEName,
pv.RestrictedQuantities,
pv.MinimumQuantity,
pv.Deleted AS VariantDeleted,
pv.CreatedOn AS VariantCreatedOn,
d.Name AS DistributorName,
d.DistributorID,
d.SEName AS DistributorSEName,
m.ManufacturerID,
m.Name AS ManufacturerName,
m.SEName AS ManufacturerSEName,
s.Name AS SalesPromptName,
p.ExtensionData,
case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice
FROM dbo.Product p with (NOLOCK)
left join dbo.ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
join @productfilter pf on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID
left join dbo.SalesPrompt s with (NOLOCK) on p.SalesPromptID = s.SalesPromptID
left join dbo.ProductManufacturer pm with (NOLOCK) on p.ProductID = pm.ProductID
left join dbo.Manufacturer m with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID
left join dbo.ProductDistributor pd with (NOLOCK) on p.ProductID = pd.ProductID
left join dbo.Distributor d with (NOLOCK) on pd.DistributorID = d.DistributorID
left join dbo.ExtendedPrice ep with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID
left join dbo.ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID
left join (select VariantID, sum(quan) quan from dbo.Inventory with (nolock) group by VariantID) i on pv.VariantID = i.VariantID
WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum)
ORDER BY pf.rownum

IF @StatsFirst <> 1
SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 08:56:40

change that order by to ORDER BY ManufacturerName to get result sorted by manufacture name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources