locked
Dynamically Create Order By Clause RRS feed

  • Question

  • User1928065626 posted

    Is there any way to dynamically create the Order By Clause? For example let's say I have a web app that displays a list of search words. Each search word corresponds to a name of a database table column.  

    The user places a check in the check box next to each word that he wants to search. Each word has an Order Of Importance number associated with it and initially, each is set to zero.

    The user can set the value of the Order of Importance number, and the word with the highest Order of Importance number is the most important.  

    Please see my example below.

    CheckBox    Search Words    Order Of Importance
    [ ] Word1 0 [x] Word2 4 [x] Word3 2 [ ] Word4 0 [x] Word5 1
    [x] Word6 3

    Then the search words and their associated order of importance numbers are passed in as arguments of a stored procedure and then sent to a database.  

    The order of importance number of zero next to a word means that the user did not place a check mark on that word so it is not sent to the database.

    The order of importance numbers will be used to create the Order By clause.

    Saturday, November 19, 2016 9:27 AM

Answers

  • User-2057865890 posted

    Hi Madjester,

    You could dynamically specify the sort order in your application code.

    create procedure uspCallAndSort
    (
        @sql varchar(2048),        --exec dbo.uspSomeProcedure arg1,'arg2',etc.
        @sortClause varchar(512)    --comma-delimited field list
    )
    AS
    insert into #tmp EXEC(@sql)
    declare @msql varchar(3000)
    set @msql = 'select * from #tmp order by ' + @sortClause
    EXEC(@msql)
    drop table #tmp
    GO

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 21, 2016 2:16 AM

All replies

  • User-2057865890 posted

    Hi Madjester,

    You could dynamically specify the sort order in your application code.

    create procedure uspCallAndSort
    (
        @sql varchar(2048),        --exec dbo.uspSomeProcedure arg1,'arg2',etc.
        @sortClause varchar(512)    --comma-delimited field list
    )
    AS
    insert into #tmp EXEC(@sql)
    declare @msql varchar(3000)
    set @msql = 'select * from #tmp order by ' + @sortClause
    EXEC(@msql)
    drop table #tmp
    GO

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 21, 2016 2:16 AM
  • User1413134711 posted

    use Case Statement like below.

    DECLARE @orderBy varchar(10)= 'Name'
    SELECT * FROM Member
    ORDER BY CASE WHEN @orderBy='Name' THEN SurName
    ELSE FirstName
    END

    Monday, November 21, 2016 6:11 AM
  • User1928065626 posted

    Hi Chris, thanks for your reply. I have come up with a solution where I passed in the order of importance numbers for and the search words. Then using a loop to sort the search words. After seeing your solution I think your method is more efficient since the sort is done in the app and not in the database.

    Monday, November 21, 2016 4:41 PM