ORDER BY in SP with multiple sort parameters

ORDER BY in SP with multiple sort parameters

Post by Dimitri Furma » Wed, 25 Sep 2002 03:28:29



Hello,

I'm writing an SP that should return a result set sorted by one, two, or
possibly more columns. The columns can be of different datatypes. The
sort order is determined by one, two, or possibly more input parameters.

should result in
ORDER BY Column1, Column2, etc. It is not known in advance how many sort
parameters may be specified.

I'm using the "orderbyvariable" technique by Richard Romley, the one with
multiple CASE statements in the ORDER BY clause. It works fine as long as
there is a single column (not counting NULLs) in the ORDER BY clause.
However, as soon as I modify my ORDER BY so that it handles the second

server doesn't like the fact that the same column name is listed more
than once in the ORDER BY clause, so I get error 169.

When there are only two sort levels, I can possibly work around this by
aliasing the columns in the SELECT statement, and using the aliases for
the first column in the ORDER BY, and base column names for the second.
Besides being a kludge, this doesn't handle the case when there are more
than two columns in the ORDER BY.

Here's the sample SP that doesn't compile. I'd appreciate any ideas on
how to make this work, or how to achieve what I'm looking for in some
other way. I cannot use dynamic SQL for this, and would prefer not to use
CAST to convert all columns to the same sortable datatype. I'm trying to
make this a generic solution that can be easily adapted to other SPs.
Thank you.

CREATE PROCEDURE procSelectInvoices




AS
SET NOCOUNT ON



SELECT  i.InvoiceNumber,
        i.InvoicedAmount,
        i.InvoicedOn,
        i.PostedOn
FROM tblInvoices AS i
WHERE 1 = 1 -- this is a sample
ORDER BY

     THEN i.InvoiceNumber ELSE NULL END ASC,

     THEN i.InvoiceNumber ELSE NULL END DESC,

     THEN i.InvoicedAmount ELSE NULL END ASC,

     THEN i.InvoicedAmount ELSE NULL END DESC,

     THEN i.InvoicedOn ELSE NULL END ASC,

     THEN i.InvoicedOn ELSE NULL END DESC,

     THEN i.PostedOn ELSE NULL END ASC,

     THEN i.PostedOn ELSE NULL END DESC,

     THEN i.InvoiceNumber ELSE NULL END ASC,

     THEN i.InvoiceNumber ELSE NULL END DESC,

     THEN i.InvoicedAmount ELSE NULL END ASC,

     THEN i.InvoicedAmount ELSE NULL END DESC,

     THEN i.InvoicedOn ELSE NULL END ASC,

     THEN i.InvoicedOn ELSE NULL END DESC,

     THEN i.PostedOn ELSE NULL END ASC,

     THEN i.PostedOn ELSE NULL END DESC

RETURN

DDL for tblInvoices:

CREATE TABLE tblInvoices (
  InvoiceNumber varchar (20) NOT NULL,
  InvoicedAmount money NOT NULL,
  InvoicedOn smalldatetime NOT NULL,
  PostedOn smalldatetime NULL
)

--
(remove a 9 to reply by email)

 
 
 

ORDER BY in SP with multiple sort parameters

Post by BP Margoli » Wed, 25 Sep 2002 12:12:45


Dimitri,

Thanks for your posting. Got to admit that I'm not positive I understand
exactly what SQL Server is really objecting to. To the best of my knowledge
ANSI SQL should allow a construct such as:

ORDER BY c1, c1

However, regardless of whether there is a bug in SQL Server or not, you
might try the following modification to your code to "fake out" SQL Server:

CREATE PROCEDURE procSelectInvoices




AS
SET NOCOUNT ON



SELECT  i.InvoiceNumber,
        i.InvoicedAmount,
        i.InvoicedOn,
        i.PostedOn
FROM tblInvoices AS i
WHERE 1 = 1 -- this is a sample
ORDER BY

     THEN i.InvoiceNumber ELSE NULL END ASC,

     THEN i.InvoiceNumber ELSE NULL END DESC,

     THEN i.InvoicedAmount ELSE NULL END ASC,

     THEN i.InvoicedAmount ELSE NULL END DESC,

     THEN i.InvoicedOn ELSE NULL END ASC,

     THEN i.InvoicedOn ELSE NULL END DESC,

     THEN i.PostedOn ELSE NULL END ASC,

     THEN i.PostedOn ELSE NULL END DESC,

     THEN i.InvoiceNumber + '' ELSE NULL END ASC,

     THEN i.InvoiceNumber + '' ELSE NULL END DESC,

     THEN i.InvoicedAmount + 0 ELSE NULL END ASC,

     THEN i.InvoicedAmount + 0 ELSE NULL END DESC,

     THEN i.InvoicedOn + 0 ELSE NULL END ASC,

     THEN i.InvoicedOn + 0 ELSE NULL END DESC,

     THEN i.PostedOn + 0 ELSE NULL END ASC,

     THEN i.PostedOn + 0 ELSE NULL END DESC

RETURN

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Hello,

> I'm writing an SP that should return a result set sorted by one, two, or
> possibly more columns. The columns can be of different datatypes. The
> sort order is determined by one, two, or possibly more input parameters.
> So

> should result in
> ORDER BY Column1, Column2, etc. It is not known in advance how many sort
> parameters may be specified.

> I'm using the "orderbyvariable" technique by Richard Romley, the one with
> multiple CASE statements in the ORDER BY clause. It works fine as long as
> there is a single column (not counting NULLs) in the ORDER BY clause.
> However, as soon as I modify my ORDER BY so that it handles the second

> server doesn't like the fact that the same column name is listed more
> than once in the ORDER BY clause, so I get error 169.

> When there are only two sort levels, I can possibly work around this by
> aliasing the columns in the SELECT statement, and using the aliases for
> the first column in the ORDER BY, and base column names for the second.
> Besides being a kludge, this doesn't handle the case when there are more
> than two columns in the ORDER BY.

> Here's the sample SP that doesn't compile. I'd appreciate any ideas on
> how to make this work, or how to achieve what I'm looking for in some
> other way. I cannot use dynamic SQL for this, and would prefer not to use
> CAST to convert all columns to the same sortable datatype. I'm trying to
> make this a generic solution that can be easily adapted to other SPs.
> Thank you.

> CREATE PROCEDURE procSelectInvoices




> AS
> SET NOCOUNT ON



> SELECT  i.InvoiceNumber,
>         i.InvoicedAmount,
>         i.InvoicedOn,
>         i.PostedOn
> FROM tblInvoices AS i
> WHERE 1 = 1 -- this is a sample
> ORDER BY

>      THEN i.InvoiceNumber ELSE NULL END ASC,

>      THEN i.InvoiceNumber ELSE NULL END DESC,

>      THEN i.InvoicedAmount ELSE NULL END ASC,

>      THEN i.InvoicedAmount ELSE NULL END DESC,

>      THEN i.InvoicedOn ELSE NULL END ASC,

>      THEN i.InvoicedOn ELSE NULL END DESC,

>      THEN i.PostedOn ELSE NULL END ASC,

>      THEN i.PostedOn ELSE NULL END DESC,

>      THEN i.InvoiceNumber ELSE NULL END ASC,

>      THEN i.InvoiceNumber ELSE NULL END DESC,

>      THEN i.InvoicedAmount ELSE NULL END ASC,

>      THEN i.InvoicedAmount ELSE NULL END DESC,

>      THEN i.InvoicedOn ELSE NULL END ASC,

>      THEN i.InvoicedOn ELSE NULL END DESC,

>      THEN i.PostedOn ELSE NULL END ASC,

>      THEN i.PostedOn ELSE NULL END DESC

> RETURN

> DDL for tblInvoices:

> CREATE TABLE tblInvoices (
>   InvoiceNumber varchar (20) NOT NULL,
>   InvoicedAmount money NOT NULL,
>   InvoicedOn smalldatetime NOT NULL,
>   PostedOn smalldatetime NULL
> )

> --
> (remove a 9 to reply by email)


 
 
 

ORDER BY in SP with multiple sort parameters

Post by Paul Thornet » Wed, 25 Sep 2002 12:11:56


I suggest that you declare a new pair of variables within your SP

concatenating all the input variables (but make them fixed length).


Now the following works (you'll need to turn it back into an SP):

DECLARE




SELECT  InvoiceNumber,
        InvoicedAmount,
        InvoicedOn,
        PostedOn
FROM    Invoices
ORDER BY
































--
Paul Thornett


> I'm writing an SP that should return a result set sorted by one,
two, or
> possibly more columns. The columns can be of different datatypes.
The
> sort order is determined by one, two, or possibly more input
parameters.
> So

> should result in
> ORDER BY Column1, Column2, etc. It is not known in advance how many
sort
> parameters may be specified.

> I'm using the "orderbyvariable" technique by Richard Romley, the one
with
> multiple CASE statements in the ORDER BY clause. It works fine as
long as
> there is a single column (not counting NULLs) in the ORDER BY
clause.
> However, as soon as I modify my ORDER BY so that it handles the
second

SQL
> server doesn't like the fact that the same column name is listed
more
> than once in the ORDER BY clause, so I get error 169.

> When there are only two sort levels, I can possibly work around this
by
> aliasing the columns in the SELECT statement, and using the aliases
for
> the first column in the ORDER BY, and base column names for the
second.
> Besides being a kludge, this doesn't handle the case when there are
more
> than two columns in the ORDER BY.

> Here's the sample SP that doesn't compile. I'd appreciate any ideas
on
> how to make this work, or how to achieve what I'm looking for in
some
> other way. I cannot use dynamic SQL for this, and would prefer not
to use
> CAST to convert all columns to the same sortable datatype. I'm
trying to
> make this a generic solution that can be easily adapted to other
SPs.
> Thank you.

> CREATE PROCEDURE procSelectInvoices


DESC


DESC
> AS
> SET NOCOUNT ON



> SELECT  i.InvoiceNumber,
>         i.InvoicedAmount,
>         i.InvoicedOn,
>         i.PostedOn
> FROM tblInvoices AS i
> WHERE 1 = 1 -- this is a sample
> ORDER BY

>      THEN i.InvoiceNumber ELSE NULL END ASC,

>      THEN i.InvoiceNumber ELSE NULL END DESC,

>      THEN i.InvoicedAmount ELSE NULL END ASC,

>      THEN i.InvoicedAmount ELSE NULL END DESC,

>      THEN i.InvoicedOn ELSE NULL END ASC,

>      THEN i.InvoicedOn ELSE NULL END DESC,

>      THEN i.PostedOn ELSE NULL END ASC,

>      THEN i.PostedOn ELSE NULL END DESC,

>      THEN i.InvoiceNumber ELSE NULL END ASC,

>      THEN i.InvoiceNumber ELSE NULL END DESC,

>      THEN i.InvoicedAmount ELSE NULL END ASC,

>      THEN i.InvoicedAmount ELSE NULL END DESC,

>      THEN i.InvoicedOn ELSE NULL END ASC,

>      THEN i.InvoicedOn ELSE NULL END DESC,

>      THEN i.PostedOn ELSE NULL END ASC,

>      THEN i.PostedOn ELSE NULL END DESC

> RETURN

> DDL for tblInvoices:

> CREATE TABLE tblInvoices (
>   InvoiceNumber varchar (20) NOT NULL,
>   InvoicedAmount money NOT NULL,
>   InvoicedOn smalldatetime NOT NULL,
>   PostedOn smalldatetime NULL
> )

 
 
 

ORDER BY in SP with multiple sort parameters

Post by Dimitri Furma » Wed, 25 Sep 2002 23:23:27




Quote:> Got to admit that I'm not positive I understand
> exactly what SQL Server is really objecting to. To the best of my
> knowledge ANSI SQL should allow a construct such as:

> ORDER BY c1, c1

Well, it looks like SQL server doesn't like that. When I modify the
ORDER BY in the SP as
ORDER BY i.InvoiceNumber, i.InvoiceNumber
I get the same error 169, "A column has been specified more than once in
the order by list. Columns in the order by list must be unique." The same
happens in a sorted view. This is SQL Server 2002 SP2, BTW. Out of
curiousity, it would be interesting to know if this also happens on older
versions.

Quote:> However, regardless of whether there is a bug in SQL Server or not,
> you might try the following modification to your code to "fake out"
> SQL Server:

This does work, thank you very much! When there are more than two sorting
levels, you have to do something like
... THEN i.InvoiceNumber + '' + '' + [...n] ELSE ...

I'm slightly uneasy as to whether or not this will continue to work in
future versions though. I guess it's not unlikely that the parser will
smarten up and figure out that trick. (Then Joe can say "Told ya!" <g>) But
I think we can live with that for the time being...

BTW, am I in any way better off using DATEADD instead of just adding 0 to a
smalldatetime value? IOW, instead of this


     THEN i.InvoicedOn + 0 ELSE NULL END ASC,

should I use this


     THEN DATEADD(mi, 0, i.InvoicedOn) ELSE NULL END ASC,

Thanks again.

--
(remove a 9 to reply by email)

 
 
 

1. Converting a database from dictionary case insensitive sort order to binary sort order

I have a database with dictionary case insensitive sort order in SQL server
7.0.

Now i need to convert that database into SQL Server 2000 with binary sort
order.

Can you please give me a best method of doing this.

FYI

I used DTS no use

I used copy database but no use

The database has 4000 tables.

Raju

2. HYPERTEXT '96 CALL

3. Need some Freq. Told Answers! (FTA)..

4. JDBC applet in IE4

5. listener problem

6. Oracle sort order on HP-UX versur Windows sort order

7. SP, dynamic Sort Order Error on chars

8. Stored Procedure: Specifying ASC or DESC sort order through a parameter

9. Sort field as Parameter to SP