Post reply

Changing "Order By" in a stored procedure

  • August 10, 2005 at 1:35 pm

    #89492

    I have a stored procedure that "should" sort a query based on an input parameter.

    I have tried to use a Case statement to dynamically change the sort order.

    Basically:

    Declare @SortOrder as varchar(10)
    Set @SortOrder = 'myString'
    Select My_ID_Int, My_Date, My_String
From MyTable
Order By CASE @SortOrder
          When 'My_ID' Then My_ID_Int
          When 'My_Date' Then My_Date
          When 'myString' Then My_String
End

    This will work for both the My_ID_Int and My_Date fields.  When I try to sort by the My_String field, I get "Syntax error converting datetime from character string."

    Can you actually create a dynamic "Order By" in the Stored Procedure?

    Thanks,

    Bryan

  • August 10, 2005 at 2:05 pm

    #581182

    Order By

    CASE When @SortOrder = 'My_ID' Then My_ID_Int ELSE NULL END,

    CASE When @SortOrder = 'My_Date' Then My_Date ELSE NULL END,

    CASE When @SortOrder = 'myString' Then My_String ELSE NULL END

  • August 10, 2005 at 2:07 pm

    #581184

    Yes you can but you will have to explicitly convert all those columns to the same datatype or separated columns

     

     


    * Noel

  • August 10, 2005 at 2:08 pm

    #581186

    I have been slow today ... Erm... like always


    * Noel

  • August 10, 2005 at 2:14 pm

    #581192

    Now why today would be any different for me .

    Also I must point out that I don't like the convert option as it screws up the order by like this for numbers >> 1143, 12, 2, 34

  • August 10, 2005 at 2:17 pm

    #581196

    Also I must point out that I don't like the convert option as it screws up the order by like this for numbers >> 12,1143, 2, 34

    true! it's just another option and for ints you have to zeropad

     


    * Noel

  • August 10, 2005 at 2:22 pm

    #581201

    Yup, but I'd go one step better : sort client side .

  • August 10, 2005 at 2:23 pm

    #581204

    That's right! I will have to agree too!

     


    * Noel

  • August 10, 2005 at 2:26 pm

    #581210

    Finally he says it .

  • August 17, 2005 at 8:43 am

    #582764

    Why not just use different code for each known sort param, e.g.

    Declare @SortOrder as varchar(10)

    Set @SortOrder = 'myString'
    IF @SortOrder = 'My_ID'
    Select My_ID_Int, My_Date, My_String
From MyTable
Order By My_ID_Int
    ELSE IF @SortOrder = 'My_Date'
    Select My_ID_Int, My_Date, My_String
From MyTable
Order By  My_Date

    ELSE IF @SortOrder = 'myString'
    Select My_ID_Int, My_Date, My_String
From MyTable
Order By My_String
    ELSE
    Select My_ID_Int, My_Date, My_String
From MyTable
     

Viewing 10 posts - 1 through 9 (of 9 total)

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