Setting dynamic Order By in Stored Procedure

  • Let's say I have a stored procedure as follows:

    CREATE PROCEDURE rotr_rptEmailCollectionReport

    @startdate as datetime,

    @enddate as datetime,

    @sorttype as int

    AS

    select

    name,

    address,

    city,

    state,

    zip,

    ordercount

    from historytable

    where

    entrydate between @startdate and @enddate

    How can I utilize the @sorttype parameter to set the order by values differently?

    Thus, if the @sorttype parameter is 0 the query should be:

    select

    name,

    address,

    city,

    state,

    zip,

    ordercount

    from historytable

    where

    entrydate between @startdate and @enddate

    order by name

    and if the @sorttype parameter value is 1 the query should be:

    select

    name,

    address,

    city,

    state,

    zip,

    ordercount

    from historytable

    where

    entrydate between @startdate and @enddate

    order by ordercount

    etc.

    How do I accomplish this in my stored proc? Right now I have a bunch of statements like this:

    if @sorttype = 0

    <query with different order by listed here>

    else if @sorttype = 1

    <query with different order by listed here>

    etc. and this is not going ot make it easy to change the stored proc later if necessary.

    Ideas?

    Thanks!

    Mike

  • Mike,

    you're going to want to build and execute your sql statement dynamically.

    Basically, you're going to build your SELECT statement as a text string. You will execute the SELECT statement using the sp_executesql stored procedure (You can find this sp within SQL Server Books Online).

    Let me know if that answers your question.

  • Hey Mike,

    Make it simple, as the other person said, use constructed queries, thats basically, u will have to arrange the SQL statement with a string variable,let say @STR, the have a string variable for SELECT in @str1, then FROM in @str2 and WHERE in @str3 and ORDER BY in @str4, So what u do for the ORDER BY is put a CASE statement and store all the combinations as a string against some values which u will be getting from the input parameter. Then concatenate these string variables in @STR and exec (@str). If you did not understand get back I shall shoot out a sample code. I have done it and it works great.

  • Thanks for the advice. The only part I did not understand how to accomplish is using the CASE statement to create the ORDER BY clause. I'd love to see some sample code of that process.

    Thanks again for all your help. This is an awesome place for SQL help! It is much appreciated. I've learned alot here already!

    Mike

  • Hi there!

    Here a sample code:

    CREATE PROCEDURE test

    @par tinyint

    AS

    select *

    from TABLE

    order by

    case @par WHEN 1 THEN field1 ELSE NULL END,

    case @par WHEN 2 THEN field2 ELSE NULL END

  • Here's an example of using a CASE statement in your ORDER BY clause. Although there're a lot of things going on in this example, hopefully, you can easily see the power of using CASE statements in clever locations and how to find ways to avoid using dynamically-generated SQL statements (even though the code looks lengthy and verbose).

    //edit: I removed the complete sproc code for brevity. Contact me if you're curious.

    
    
    ...
    ORDER BY
    -- #### STRING COLUMNS #### --
    CASE @sortColOrdinal
    WHEN 0 THEN Topic_Name
    WHEN 1 THEN (CASE b.Topic_Type WHEN 0 THEN 'Web Document' WHEN 1 THEN 'File' WHEN 2 THEN 'Web Link' ELSE '' END)
    WHEN 2 THEN Type1_FileName
    WHEN 5 THEN (SELECT COALESCE(Language_PrettyName,'') FROM app_languages WHERE ID = b.Language_ID)
    WHEN 6 THEN (SELECT COALESCE(Status_Name,'') FROM Repository_Status WHERE ID = a.Status_ID)
    WHEN 7 THEN (SELECT COALESCE(UserName,'') FROM User_Login WHERE ID = a.Lock_Owner_ID)
    ELSE ''
    END,
    -- #### DATETIME COLUMNS #### --
    CASE @sortColOrdinal
    WHEN 3 THEN a.Date_Last_Modified
    WHEN 4 THEN a.Date_Created
    ELSE ''
    END,
    -- #### NUMERIC COLUMNS #### --
    CASE @sortColOrdinal
    WHEN 8 THEN (SELECT COUNT(x.ID) FROM Website_Element x INNER JOIN Website_Element_Data y ON y.Element_ID = x.ID INNER JOIN Website_Element_Promotion z ON z.Element_Data_ID = y.ID And Promotion_State_ID > 0 WHERE y.Repository_Topic_Details_ID = b.ID )
    ELSE ''
    END,
    Topic_Name,
    b.Date_Last_Modified

    ...

    Edited by - kenwallacedesign on 09/03/2003 01:49:08 AM

  • I've given this a try and here is the case statement I've come up with for the order by clause:

    <procedure declaration and code here then the order by...>

    order by

    (

    case @sorttype

    when 0 then employees.fullname

    when 1 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id)

    when 2 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and isnull(email, '') <> '')

    when 3 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck <= @workingdate and isnull(email, '') = '')

    when 4 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck between DateAdd(d, 1, @workingdate) and DateAdd(d, 180, @workingdate) and isnull(email, '') = '')

    when 5 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck = '12/31/2099' and isnull(email, '') = '')

    else

    ''

    end

    )

    set nocount off

    GO

    [/code]

    The part where it says "when 2 then..." causes an error:

    Syntax error converting the varchar value 'Poleski, Thomas' to a column of data type int.

    What's up with that? How can I fix this problem?

    Thanks!

    Mike

  • IN addition, the procedure I wrote has some columns defined as follows:

    (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id) as CustomersAsked,

    (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and isnull(email, '') <> '') as CapturedEmails,

    (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck <= @workingdate and isnull(email, '') = '') as AskNextTime,

    (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck between DateAdd(d, 1, @workingdate) and DateAdd(d, 180, @workingdate) and isnull(email, '') = '') as AskSixMonths,

    (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck = '12/31/2099' and isnull(email, '') = '') as NeverAskAgain

    and I'd like to be able to ORDER BY EmailsCaptured or one of the other named columns, but when I try this in a case statement as follows:

    case @sorttype

    when 1 then EmailsCaptured

    when 2 then NeverAskAgain

    else

    ''

    end

    I get an error telling me that "EmailsCaptured" and "NeverAskAgain" are not valid column names. How can I accomplish THIS task?

    Thanks!

    Mike

  • Mike,

    When a CASE statement is used in your ORDER BY, SQL Server will return errors if the columns referenced in your CASE statement are not all of similar data types.

    If you notice in my example sproc, I placed three comma-separated CASE statements in a row in my ORDER BY clauses. This is a workaround to this issue.

    quote:


    
    
    ORDER BY
    -- #### STRING COLUMNS #### --
    CASE @sortColOrdinal
    WHEN 0 THEN Topic_Name
    WHEN 1 THEN (CASE b.Topic_Type WHEN 0 THEN 'Web Document' WHEN 1 THEN 'File' WHEN 2 THEN 'Web Link' ELSE '' END)
    WHEN 2 THEN Type1_FileName
    WHEN 5 THEN (SELECT COALESCE(Language_PrettyName,'') FROM app_languages WHERE ID = b.Language_ID)
    WHEN 6 THEN (SELECT COALESCE(Status_Name,'') FROM Repository_Status WHERE ID = a.Status_ID)
    WHEN 7 THEN (SELECT COALESCE(UserName,'') FROM User_Login WHERE ID = a.Lock_Owner_ID)
    ELSE ''
    END,
    -- #### DATETIME COLUMNS #### --
    CASE @sortColOrdinal
    WHEN 3 THEN a.Date_Last_Modified
    WHEN 4 THEN a.Date_Created
    ELSE ''
    END,
    -- #### NUMERIC COLUMNS #### --
    CASE @sortColOrdinal
    WHEN 8 THEN (SELECT COUNT(x.[ID]) FROM Website_Element x INNER JOIN Website_Element_Data y ON y.Element_ID = x.[ID] INNER JOIN Website_Element_Promotion z ON z.Element_Data_ID = y.[ID] And Promotion_State_ID > 0 WHERE y.Repository_Topic_Details_ID = b.[ID] )
    ELSE ''
    END,
    Topic_Name,
    b.Date_Last_Modified


    Edited by - kenwallacedesign on 09/03/2003 01:06:10 AM

  • Mike,

    All arguments in the CASE statement used in your ORDER BY clause must be actual column names, not aliases.

    In your situation, the following syntax should do the trick:

    
    
    ORDER BY
    -- #### STRING COLUMNS #### --
    CASE @sorttype
    WHEN 0 THEN employees.fullname
    ELSE ''
    END,
    -- #### NUMERIC COLUMNS #### --
    CASE @sorttype
    WHEN 1 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id)
    WHEN 2 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and isnull(email, '') <> '')
    WHEN 3 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck <= @workingdate and isnull(email, '') = '')
    WHEN 4 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck between DateAdd(d, 1, @workingdate) and DateAdd(d, 180, @workingdate) and isnull(email, '') = '')
    WHEN 5 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck = '12/31/2099' and isnull(email, '') = '')
    ELSE ''
    END,
    employees.fullname

    In the above code, I added a third ORDER BY clause. This way, in case the @sorttype resolves to one of the numeric columns, you are still doing a second sort based on alphabetical fullname. Take that out if you don't need your results formatted in this fashion.

    I know it looks redundant to post each column data type in it's own CASE statement, but if you can visualize the end result of all the CASE statements combined it becomes more clear.

    Imagine that @sorttype is 1, you are actually saying:

    
    
    ORDER BY
    '',
    (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id),
    employees.fullname

    .

    Edited by - kenwallacedesign on 09/03/2003 01:29:36 AM

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

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