Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Dyanamic 'Order BY' in Stored Procedure

    Hmm
    I can't figure out why this won't work.
    I want to use a variable for the 'ORDER BY' in my Stored Procedure.
    I use this to order my results by product price or alphabetically from a dropdown menu.
    I have tried the following, but get the error below.
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[SPResults]
    @Orderby as varchar(50),
    @Parm1 as varchar(255),
    @Parm2 as int
    as
    
    SELECT ClientProducts.ClientID, FT_TBL.ProductID, FT_TBL.ManufacturerID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, KEY_TBL.RANK, 
    COUNT(ClientOffers.ProductID) AS QtyOffers
    FROM dbo.Products
    AS FT_TBL 
    INNER JOIN dbo.ClientProducts 
    ON FT_TBL.ProductID = ClientProducts.ProductID
    LEFT OUTER JOIN 
    ClientOffers ON FT_TBL.ProductID = ClientOffers.ProductID 
    AND ClientOffers.ClientID = ClientProducts.ClientID 
    INNER JOIN CONTAINSTABLE(dbo.Products, *, 
    @Parm1, @Parm2) AS KEY_TBL
    ON FT_TBL.ProductID = KEY_TBL.[KEY]
    WHERE (ClientProducts.ClientID = 0)
    GROUP BY dbo.ClientProducts.ClientID, ClientOffers.ProductID, FT_TBL.ProductID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, FT_TBL.ManufacturerID, KEY_TBL.RANK
    ORDER BY + @Orderby
    error
    Code:
    Msg 1008, Level 16, State 1, Procedure SPResults, Line 21
    The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
    Is this possible to do?

    Thank you
    Andy

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not in that fashion. You can use a CASE statement in the order by clause, but I am not entirely certain what that may do to your performance, of ordering one way generates a different query plan from ordering a different way.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The ordering of the data is a presentation issue and should really be handled by your application interface, not your stored procedure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If there are a small number of possibilities for the order by clause, add a parameter to select which one you want to use and add "if" groups:

    IF @OrderParameter = 1
    BEGIN
    [your query]
    END
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •