SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Trying to amend a stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deanglen
Yak Posting Veteran

64 Posts

Posted - 02/13/2013 :  04:21:51  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/13/2013 :  05:06:36  Show Profile  Reply with Quote
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/

Go to Top of Page

deanglen
Yak Posting Veteran

64 Posts

Posted - 02/13/2013 :  05:38:11  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2013 :  05:45:45  Show Profile  Reply with Quote
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/

Go to Top of Page

deanglen
Yak Posting Veteran

64 Posts

Posted - 02/20/2013 :  07:22:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  08:04:25  Show Profile  Reply with Quote
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/

Go to Top of Page

deanglen
Yak Posting Veteran

64 Posts

Posted - 02/20/2013 :  08:14:51  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  08:56:40  Show Profile  Reply with Quote
change that order by to ORDER BY ManufacturerName to get result sorted by manufacture name

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000