Hopw can you make a dynamic Order By clause in a stored procedure?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: Hopw can you make a dynamic Order By clause in a stored procedure?

  1. #1
    Dan Zettle Guest

    Hopw can you make a dynamic Order By clause in a stored procedure?


    Is there any way that you can dynamically change the columns specified in
    the order by clause of a stored procedure that runs a select statement?
    What I would like to do is allow a parameter to be passed that contains an
    order by clause and have that order by clause be used by the select statement.
    The problem that I am encountering is that you cannot use variables within
    the order by clause of a select statement within a stored procedure, so I
    cannot use an order by clause passed as a parameter. Is there any way to
    accomplish this, or is the only way to get dynamic sorting is by creating
    an entire SQL statement and the running it from an ADO command object that
    has the CommandType property set to adCmdText. Help!...and thanks.

  2. #2
    Michael Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?


    Try using dynamic sql.

    CREATE PROCEDURE usp_flexorderby
    @orderbyclause varchar(100)
    as
    DECLARE @cmd varchar(2000)
    SET @cmd = 'SELECT * FROM authors ORDER BY ' + @orderbyclause

    EXEC(@cmd)

    -Mike


  3. #3
    C. E. Buttles Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?

    Bear in mind that you can be inviting deadlocks if you have more than one
    user.


    "Michael" <michaell@gasullivan.com> wrote in message
    news:38d51410$1@news.devx.com...
    >
    > Try using dynamic sql.
    >
    > CREATE PROCEDURE usp_flexorderby
    > @orderbyclause varchar(100)
    > as
    > DECLARE @cmd varchar(2000)
    > SET @cmd = 'SELECT * FROM authors ORDER BY ' + @orderbyclause
    >
    > EXEC(@cmd)
    >
    > -Mike
    >



  4. #4
    Michael Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?


    Do you know of an issue regarding dynamic SQL and deadlocks?

    -Mike


  5. #5
    C. E. Buttles Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?

    The issue is with changing the order of addressing the columns. If one
    query goes at it from left to right for example and another is accessing the
    same table from the opposite direction, they will eventually meet, supposing
    they are querying the same columns, and a deadlock will occur while one is
    waiting for the other to finish, and vice versa. This is in the standard
    discourse on deadlocks in BOL and has been the root of many problems with
    third-party apps I have encountered. The prevailing wisdom is to access the
    columns consistently in the same order to prevent this. Varying which
    columns to access is not the problem; the order in which they are accessed
    is.


    "Michael" <michaell@gasullivan.com> wrote in message
    news:38d690fd$1@news.devx.com...
    >
    > Do you know of an issue regarding dynamic SQL and deadlocks?
    >
    > -Mike
    >



  6. #6
    C. E. Buttles Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?

    On reflection, he may not have a problem as it is the ORDER BY statement
    involved. The problem I was addressing is in the SELECT statement. Mea
    gulpa!


    "Michael" <michaell@gasullivan.com> wrote in message
    news:38d690fd$1@news.devx.com...
    >
    > Do you know of an issue regarding dynamic SQL and deadlocks?
    >
    > -Mike
    >



  7. #7
    Michael Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?


    I still don't see how the order by which the columns are access can cause
    a deadlock especially since SQL Server aquires a Shared lock while reading
    and the lock is removed after the resource (ie, row probably) is read.

    Most deadlock situations occur amoung processes that are updating data.

    -Mike


  8. #8
    C. E. Buttles Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?

    You are quite correct. It is when updating. Perhaps I didn't clarify it
    enough.
    Thank you.


    "Michael" <michaell@gasullivan.com> wrote in message
    news:38d7771e$1@news.devx.com...
    >
    > I still don't see how the order by which the columns are access can cause
    > a deadlock especially since SQL Server aquires a Shared lock while reading
    > and the lock is removed after the resource (ie, row probably) is read.
    >
    > Most deadlock situations occur amoung processes that are updating data.
    >
    > -Mike
    >



  9. #9
    jo Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?


    Hi Dan,
    Another way of doing this if there is a finite number of columns that the
    sort could be on is to use something like this:

    Create procedure spOrderBy
    @Col1 integer = Null, --datatype compatible w/column1 sort col
    @Col2 integer = Null, --datatype compatible w/column2 sort col
    @Col3 integer = Null,
    @Col4 integer = Null,
    @Col5 integer = Null
    AS
    Select * from MyTable
    Order by IsNull(@Col1, column1),
    IsNull(@Col2, column2),
    IsNull(@Col3, column3),
    IsNull(@Col4, column4),
    IsNull(@Col5, column5)

    Accepting the null default values of the parameters would sort by all 5 columns
    in that order. If you want a column in the order by, you leave its parameter
    Null; if you DON'T want it in the order-by, you specify some other value
    for the parameter (it doesn't really matter what). Of course, this approach
    works best if the order of your order-by columns doesn't change. For example,
    if anytime you need to sort by State and Zip, you always need it by State
    first and then by Zip, you are fine, but if you sometimes need Zip first
    and then State, then this approach becomes a bit more cumbersome. You can
    still accomplish it by having State twice, e.g.

    Order by IsNull(@State1, MyState),
    IsNull(@Zip, MyZip),
    IsNull(@State2, MyState)

    and when you need State, Zip, you specify @State1 = Null, @Zip = Null, and
    @State2 = 'X'. But when you need Zip first and then State, you specify @State1
    = 'X', @Zip = Null, and @State2 = Null. However, in my travels I have rarely
    needed this -- generally when our data needs to be sorted by more than one
    column, those columns are usually hierarchical and always appear in the same
    order. However, if you're doing something really complex, this approach
    might not work for you, and you might be better off using the EXEC(@SQL)
    approach already suggested.

    Just another idea to think about!


    "Dan Zettle" <dan.zettle@shoptok.com> wrote:
    >
    >Is there any way that you can dynamically change the columns specified

    in
    >the order by clause of a stored procedure that runs a select statement?


    >What I would like to do is allow a parameter to be passed that contains

    an
    >order by clause and have that order by clause be used by the select statement.
    > The problem that I am encountering is that you cannot use variables within
    >the order by clause of a select statement within a stored procedure, so

    I
    >cannot use an order by clause passed as a parameter. Is there any way to
    >accomplish this, or is the only way to get dynamic sorting is by creating
    >an entire SQL statement and the running it from an ADO command object that
    >has the CommandType property set to adCmdText. Help!...and thanks.



  10. #10
    Dan Zettle Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?


    Micheal,

    Thanks you very much for your code sample. I have yet to try it out but
    I should be able to test it this morning. This is the first time I have
    asked a question on an open board like this, I and I am very pleased that
    there are those like you that help out. Thanks a lot!

    Sincerely,

    Dan.

    "Michael" <michaell@gasullivan.com> wrote:
    >
    >Try using dynamic sql.
    >
    >CREATE PROCEDURE usp_flexorderby
    > @orderbyclause varchar(100)
    >as
    > DECLARE @cmd varchar(2000)
    > SET @cmd = 'SELECT * FROM authors ORDER BY ' + @orderbyclause
    >
    > EXEC(@cmd)
    >
    >-Mike
    >



  11. #11
    Tracey Wilson Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?


    I've used this dynamic where and order-by clause in my sp's, but one thing
    that I've just wondered, is does sql 7 know how to pre-complile this sp if
    its dynamic? Any clues?

    Trace

    "jo" <jojoa0@my-deja.com> wrote:
    >
    >Hi Dan,
    >Another way of doing this if there is a finite number of columns that the
    >sort could be on is to use something like this:
    >
    >Create procedure spOrderBy
    > @Col1 integer = Null, --datatype compatible w/column1 sort col
    > @Col2 integer = Null, --datatype compatible w/column2 sort col
    > @Col3 integer = Null,
    > @Col4 integer = Null,
    > @Col5 integer = Null
    >AS
    >Select * from MyTable
    >Order by IsNull(@Col1, column1),
    >IsNull(@Col2, column2),
    >IsNull(@Col3, column3),
    >IsNull(@Col4, column4),
    >IsNull(@Col5, column5)
    >
    >Accepting the null default values of the parameters would sort by all 5

    columns
    >in that order. If you want a column in the order by, you leave its parameter
    >Null; if you DON'T want it in the order-by, you specify some other value
    >for the parameter (it doesn't really matter what). Of course, this approach
    >works best if the order of your order-by columns doesn't change. For example,
    >if anytime you need to sort by State and Zip, you always need it by State
    >first and then by Zip, you are fine, but if you sometimes need Zip first
    >and then State, then this approach becomes a bit more cumbersome. You can
    >still accomplish it by having State twice, e.g.
    >
    >Order by IsNull(@State1, MyState),
    >IsNull(@Zip, MyZip),
    >IsNull(@State2, MyState)
    >
    >and when you need State, Zip, you specify @State1 = Null, @Zip = Null, and
    >@State2 = 'X'. But when you need Zip first and then State, you specify

    @State1
    >= 'X', @Zip = Null, and @State2 = Null. However, in my travels I have rarely
    >needed this -- generally when our data needs to be sorted by more than one
    >column, those columns are usually hierarchical and always appear in the

    same
    >order. However, if you're doing something really complex, this approach
    >might not work for you, and you might be better off using the EXEC(@SQL)
    >approach already suggested.
    >
    >Just another idea to think about!
    >
    >
    >"Dan Zettle" <dan.zettle@shoptok.com> wrote:
    >>
    >>Is there any way that you can dynamically change the columns specified

    >in
    >>the order by clause of a stored procedure that runs a select statement?

    >
    >>What I would like to do is allow a parameter to be passed that contains

    >an
    >>order by clause and have that order by clause be used by the select statement.
    >> The problem that I am encountering is that you cannot use variables within
    >>the order by clause of a select statement within a stored procedure, so

    >I
    >>cannot use an order by clause passed as a parameter. Is there any way

    to
    >>accomplish this, or is the only way to get dynamic sorting is by creating
    >>an entire SQL statement and the running it from an ADO command object that
    >>has the CommandType property set to adCmdText. Help!...and thanks.

    >



  12. #12
    Pablo Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?


    "Michael" <michaell@gasullivan.com> wrote:
    >
    >Try using dynamic sql.
    >
    >CREATE PROCEDURE usp_flexorderby
    > @orderbyclause varchar(100)
    >as
    > DECLARE @cmd varchar(2000)
    > SET @cmd = 'SELECT * FROM authors ORDER BY ' + @orderbyclause
    >
    > EXEC(@cmd)
    >
    >-Mike
    >


    Mike:

    Just to remind that to run this query, your user not only needs Execute permission
    on the procedure but ALSO Select permission on the authors table. Depending
    on your implementation this can put a security hole.

    Also the procedure is not pre-processed on save but on each run.

  13. #13
    Dale Guest

    Re: Hopw can you make a dynamic Order By clause in a stored procedure?


    Hi,
    Just a question on this.
    What would happen if updates were taking place on this table at the same
    time that the stored procedure was trying to order the rows in the sequence
    specified by the parameter, but the column order of the updates is different
    to the ordering sequence? Might this cause deadlock problems since shared
    locks and exclusive lockes are incompatible?


    "C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
    >You are quite correct. It is when updating. Perhaps I didn't clarify it
    >enough.
    >Thank you.
    >
    >
    >"Michael" <michaell@gasullivan.com> wrote in message
    >news:38d7771e$1@news.devx.com...
    >>
    >> I still don't see how the order by which the columns are access can cause
    >> a deadlock especially since SQL Server aquires a Shared lock while reading
    >> and the lock is removed after the resource (ie, row probably) is read.
    >>
    >> Most deadlock situations occur amoung processes that are updating data.
    >>
    >> -Mike
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center