Hello,

I need to provide sorting ability across multiple column.

Data which would be sorted are retrieved with stored procedure.

I have two solutions:

1. if statement
depending of sort type I must create diferent select statement

in case many columns there would be so many variations (also, there must be
considered sort direction ASC or DESC what increase variations double)

2. dynamic sql

Is there any additional way, which one is better?

Please advice,

Best regards

On Jan 7, 7:52*am, "m" <mirosla... (AT) gmail (DOT) com> wrote:
Generally speaking static SQL is preferable to dynamic SQL. However,
there are times when dynamic SQL comes in handy. If you have only a
couple of possible orderings I would go with static SQL. If you have
numerous possible desired orderings with varying order by column lists
then I would build the order by clause in the procedure logic.

Just make sure you are not trying to do too much in a single procedure
and that all the SQL's in question are really just variations of one
basic query rather than trying to code logic to accomodate several
different basic queries that just happen to use the same source
tables.

HTH -- Mark D Powell --

You can also use CASE expressions. For example, if the data type of the columns is the same:

ORDER BY CASE @column
WHEN 'column1' THEN column1
WHEN 'column2' THEN column2
WHEN 'column3' THEN column3
END

If the data types are different then you have to use multiple CASE expressions:

ORDER BY CASE @column WHEN 'column1' THEN column1 END,
CASE @column WHEN 'column2' THEN column2 END,
CASE @column WHEN 'column3' THEN column3 END

But using dynamic SQL may be best as appropriate indexes on the ordering columns can be used more efficiently.

--
Plamen Ratchev
http://www.SQLStudio.com

Thank you.

"Mark D Powell" <Mark.Powell2 (AT) hp (DOT) com> wrote

On Jan 7, 7:52 am, "m" <mirosla... (AT) gmail (DOT) com> wrote:
Generally speaking static SQL is preferable to dynamic SQL. However,
there are times when dynamic SQL comes in handy. If you have only a
couple of possible orderings I would go with static SQL. If you have
numerous possible desired orderings with varying order by column lists
then I would build the order by clause in the procedure logic.

Just make sure you are not trying to do too much in a single procedure
and that all the SQL's in question are really just variations of one
basic query rather than trying to code logic to accomodate several
different basic queries that just happen to use the same source
tables.

HTH -- Mark D Powell --

Thank you.

"Plamen Ratchev" <Plamen (AT) SQLStudio (DOT) com> wrote