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

[Dan Zettle]

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.

[Michael]

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

[C. E. Buttles]

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
>

[Michael]

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

-Mike

[C. E. Buttles]

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
>

[C. E. Buttles]

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
>

[Michael]

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

[C. E. Buttles]

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
>

[jo]

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.

[Dan Zettle]

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
>

[Tracey Wilson]

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.
>

[Pablo]

"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.

[Dale]

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
>>
>