Dynamic Order By in 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

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Order By in Stored Procedure

Author

Topic

Mr Fett
Starting Member

28 Posts

Posted - 2008-07-30 : 14:31:48

Hi all,

I'm trying to adapt a store procedure I have to allow a dynamic order by - I know I can't just pass a variable containing the ORDER BY and setup a CASE statement. What I don't understand is that if I hardcode an ORDER BY for PRODUCTNAME it's fine - if I have it in a select statement I get an error saying

'ORDER BY ITEMS MUST APPEAR IN THE SELECT STATEMENT IF SELECT DISTINCT is specificed'

The frustrating thing is that 'productName' IS in the select statement - proved by the fact that it works fine if its hardcoded

My stored procedure is below:

CREATE PROCEDURE dbo.myStoreProcedure
@categoryID INT,
@orderByVal VARCHAR

AS

SELECT DISTINCT PRODUCT_products.productName, PRODUCT_products.seoName, PRODUCT_products.seoCatPath, PRODUCT_products.productSummary, PRODUCT_products.price, PRODUCT_products.thumbWidth, PRODUCT_products.thumbHeight, PRODUCT_products.imageAvailable, PRODUCT_products.productID, PRODUCT_products.categoryID, PRODUCT_productVariations.specialOffer, PRODUCT_productVariations.salePrice

FROM PRODUCT_products INNER JOIN PRODUCT_productVariations ON PRODUCT_products.productID = PRODUCT_productVariations.productID, PRODUCT_categories AS cat1, PRODUCT_categories AS cat2, PRODUCT_categories AS cat3
WHERE PRODUCT_products.visible = 1
AND ((PRODUCT_products.categoryID = cat1.categoryID AND
cat1.parentCategoryID = cat2.categoryID AND
cat2.parentCategoryID = cat3.categoryID AND
cat3.categoryID = @categoryID AND cat3.amParent = 1) OR

(PRODUCT_products.categoryID = cat1.categoryID AND
cat1.parentCategoryID = cat2.categoryID AND
cat2.categoryID = @categoryID AND cat2.amParent = 1))

ORDER BY CASE @orderByVal
WHEN 'productName' THEN PRODUCT_products.productName
WHEN 'price' THEN PRODUCT_products.price
END
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 14:36:52

I think this should do it:
ORDER BY CASE WHEN @orderByVal = 'productName' THEN PRODUCT_products.productName
WHEN @orderByVal = 'price' THEN PRODUCT_products.price
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-30 : 14:37:46

First, read this: http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx

And then this:

http://weblogs.sqlteam.com/jeffs/archive/2007/05/03/60195.aspx

Your SELECT is NOT "dynamically" ordering by either the productName or price column depending on some condition; it is ALWAYS ordering by the following expression:


CASE @orderByVal
WHEN 'productName' THEN PRODUCT_products.productName
WHEN 'price' THEN PRODUCT_products.price
END

(this is explained in the second link I gave you)

Thus, if you want to use DISTINCT but also order by that expression, you must include that expression in your SELECT clause. Not the two columns -- ProductName and Price -- but the CASE expression itself. (The reason why is explained in the first link I gave you)

Also, on a side note, why are you using DISTINCT? Usually that means you are either missing a JOIN somewhere, or you should be doing some grouping somewhere.

- Jeff
http://weblogs.sqlteam.com/JeffS

Mr Fett
Starting Member

28 Posts

Posted - 2008-07-31 : 14:44:30

Hi all,

Thanks for the responses - much appreciated - working through it now!

Bob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources