Very Computer

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)

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.

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

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)