Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809

    Unanswered: Dynamic Order By

    I am trying to write a query that would allow me to switch the order of the result set by two columns and whether the order is ascending or descending. So far I have:

    Code
    --------------------------------------------------------------------------------------------------
    declare @Case char(1)
    set @Case = 'A'
    select * from marketdata.dbo.dictionary
    order by case when @Case = 'A' then ParentID ASC else ParentID DESC end
    , case when @Case = 'A' then MemberID ASC else MemberID DESC end

    --------------------------------------------------------------------------------------------------
    My problem is that I get an error on the "ASC" or "DESC".

    Thanks for any help offered!
    Paul Young
    (Knowledge is power! Get some!)

  2. #2
    Join Date
    Feb 2002
    Posts
    14
    Hi, We do it a little differently, we have a stored procedure that accepts a sort variable. The sort variable determines the select used for the query. Here's part of the stored procedure

    CREATE Procedure GetAllEmployees
    (
    @sort char(1)
    /* F = First, Last
    L = Last, First
    D = Deparment, First, Last
    O = Location, First, Last
    */
    )

    As
    if @sort = 'D' begin
    Select
    PL.UID ,
    PL.NTUserID,
    PL.FirstName ,
    PL.LastName ,
    PL.Department,
    LO.LocationName ,
    DP.DepartmentName ,
    PL.Extension ,
    PL.FaxNumber,
    PL.Email ,
    PL.PagerEmail ,
    PL.PagerNumber,
    PL.HomePhone1,
    PL.HomePhone2,
    PL.CellPhone,
    PL.bManager,
    PL.bVP

    from Employees PL inner join Locations LO on PL.Location = LO.UID inner join Departments DP on PL.Department = DP.DepartmentNumber
    where bActive="Y"
    order by PL.department, firstname, lastname
    end

    else if @sort = 'L' begin

    Select

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I currently have the functional equivilant of your code. The problem is that someone made a change to two of the selects but missed the last one. I was trying to consolidate the code to improve maintance. Thanks!
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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