Order By using a Variable

  • I have created a Stored Procedure that I would like to have a dynamic sort.  Here's an example:

    create procedure [dbo].[sp_query] (

    @sort   varchar(20)

    )

    AS

    Select * From Table

    Order by @sort

     

    Upon validating the query, it pops an error saying that that sort must be against a Column name.  How can I do this?

    Thanks!

  • you'll either need to use dynamic SQL, which should generally be avoided, or use a if-then-else statement like so:

    dynamic SQL:

    @SQL='SELECT * FROM TABLENAME ORDER BY ' + @sort

    EXECUTE(@SQL)

    Logical parameters.

    create procedure [dbo].[sp_query] (@sort   varchar(20) )

    AS

     

    if @sort='FIRSTNAME'

    Select * From Table order by FIRSTNAME

    elseif @sort='PHONENBR'

    Select * From Table order by 'PHONENBR'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • The way I got around this was by using a case by statement in the order by clause.  Here is an example:

    CREATE PROCEDURE Return_EmpInfo ( @OrderColumn varchar(100) ) AS

    SELECT EmpID, FName, LName, Title

    FROM Employee

    ORDER BY

      CASE WHEN @OrderColumn = 'LName' THEN LName

           WHEN @OrderColumn = 'FName' THEN FName

           WHEN @OrderColumn='Title' THEN Title

      END

    Hope this helps.

  • Thank you very much for the suggestions!  If I went the route of dynamic sql, how would I deal with single quotes within the quotes?  Such as:

    @sql = ' select company, contact, cast(numfield as varchar(20)) as 'Number' from table where company like '%inc%' '

    Thanks again!

  • Make sure SET QUOTED_IDENTIFIER { ON | OFF } is set to OFF and use double quotes around your Dynamic SQL statement. 

    Also, read the following:  <A href="Curses'>http://www.sommarskog.se/dynamic_sql.html">Curses & Blessings of Dynamic SQL</A>

     

    (I still can't seem to get it...  )

    I wasn't born stupid - I had to study.

  • 'kay farrell - trying once again - type (all bolded words) exactly in the order given below:

    1) start with lesser than sign followed by a - then single space

    2) href="http://www.sommarskog.se/dynamic_sql.html"

    3) greater than sign

    4) Curses and Blessings of Dynamic SQL

    5) lesser than sign - /a - greater than sign

    ..only one space in the whole line (#1)







    **ASCII stupid question, get a stupid ANSI !!!**

  • Thanks for being "Committed". 

    <a href="Curses'>http://www.sommarskog.se/dynamic_sql.html">Curses & Blessings of Dynamic SQL</a>

     

    (I think I must be terminal...)   

    I wasn't born stupid - I had to study.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply