Dynamically Build Order By

  • I think someone posted about this before but I still appreciate any help on it.

    I am attempting to build the SELECT statement with the variable being the "Order By". For example, I would like the user to pick any one out of the 5 columns in the table and select all records from the table ordering by (for this example purposes, ASC order) the column chosen.

    I understand that you can build SQL syntax and then execute it. But I am trying to avoid using static string. Your help is appreciated.

  • Use a CASE expression, e.g.:

    
    
    ...
    ORDER BY CASE @OrderBy
    WHEN 'Col1' THEN Col1
    WHEN 'Col2' THEN Col2
    WHEN 'Col3' THEN Col3
    ELSE 'Col4' END

    If the columns are not implicitly convertible to the same data types, you will also need to use CAST or CONVERT with the column values.

    --Jonathan



    --Jonathan

  • Thank you.

  • Amother useful technique is to shift the case statement to the select-list, give it an alias and then use the alias in the ORDER BY clause.

    select case @Order when 'LNAME' then au_lname

    when 'FNAME' then au_fname

    else au_id

    endas SortKey,

    authors.*

    from authors order by SortKey

    This can be handy when you have a client application capable of (say) reversing the sort order - the application can do this on the returned data without re-querying.

  • how about Multiple columns

    such as

    declare @OrderBy varchar(200)

    set @OrderBy = 'FIRST_NAME,LAST_NAME'

    SELECT * FROM RESOURCE

    order by case @OrderBy

    WHEN 'FIRST_NAME,LAST_NAME' THEN FIRST_NAME,LAST_NAME

    WHEN 'LAST_NAME,FIRST_NAME' THEN LAST_NAME,FIRST_NAME

    END

    It will give you a sql error

  • If you execute sql you can do whatever combinations you need:

    CREATE PROCEDUREsproc_getAllForumThreads

    @SortBynvarchar(255),

    @OrderTypebit

    AS

    DECLARE @Order varchar(4), @sql varchar(1000)

    IF @OrderType = 0

    BEGIN

    SET @Order = 'ASC'

    END

    ELSE

    BEGIN

    SET @Order = 'DESC'

    END

    SET @sql = 'SELECT Name, Email, Posted, Replied, Followed, Actual, Subject, MessageID, Message, Guestbook FROM Forum ORDER BY ' + @SortBy + ' ' + @Order + ', Replied, Followed ASC'

    EXEC(@sql)

  • quote:


    how about Multiple columns

    such as

    declare @OrderBy varchar(200)

    set @OrderBy = 'FIRST_NAME,LAST_NAME'

    END SELECT * FROM RESOURCE

    order by case @OrderBy

    WHEN 'FIRST_NAME,LAST_NAME' THEN FIRST_NAME,LAST_NAME

    WHEN 'LAST_NAME,FIRST_NAME' THEN LAST_NAME,FIRST_NAME

    It will give you a sql error


    
    
    SELECT *
    FROM RESOURCE
    ORDER BY CASE @OrderBy
    WHEN 'FIRST_NAME,LAST_NAME' THEN FIRST_NAME
    WHEN 'LAST_NAME,FIRST_NAME' THEN LAST_NAME
    ELSE @OrderBy
    END,
    CASE @OrderBy
    WHEN 'FIRST_NAME,LAST_NAME' THEN LAST_NAME
    WHEN 'LAST_NAME,FIRST_NAME' THEN FIRST_NAME
    ELSE @OrderBy
    END

    --Jonathan



    --Jonathan

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

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