Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1158

Re: Puzzling ORDER BY

Show key headers only | View raw

On Thursday, June 21, 2012 5:37:54 PM UTC+1, Gene Wirchenko wrote:
> Dear SQLers:
> 
> http://www.mssqltips.com/sqlservertip/1455/using-the-case-expression-instead-of-dynamic-sql-in-sql-server/
> has an example with some puzzling code.  Example 3's code is:
> 
> ***** Start of Code *****
> CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9),
> @sortdirection CHAR(4)
> AS
> SET nocount ON
> 
> SELECT customerid, firstname, lastname, statecode, statedescription,
> totalsales
> FROM dbo.Customer
> ORDER BY 
> CASE @sortdirection
>      WHEN 'asc' THEN
>       CASE @sortby 
>        WHEN 'firstname' THEN firstname 
>        WHEN 'lastname' THEN lastname 
>        END
> END 
> ASC,
> CASE @sortdirection
>       WHEN 'desc' THEN
>        CASE @sortby 
>        WHEN 'firstname' THEN firstname 
>        WHEN 'lastname' THEN lastname 
>        END
> END
> DESC
> GO
> 
> EXEC dbo.getCustomerData 'lastname', 'desc'
> ***** End of Code *****
> 
>      This looks like it specifies an order of two columns one
> ascending and one descending but with one of them null.  How does this
> work?  The syntax is weird (to me).
> 
> Sincerely,
> 
> Gene Wirchenko

Has the question been answered?

The code is a means of writing a single 
SQL statement that can operate as 
"ORDER BY firstname ASC" or "BY lastname ASC"
or "BY firstname DESC" or "BY lastname DESC",
but everyone thinks it's going to run like 
a dead Galapagos turtle in real life, if
SQL Server can't decipher the logic and 
split out the actual meaningful columns in 
the formula - and we seem to assume that 
it can't.

Of course you can "ORDER BY columnWhichIsNull"
which will be random order unless you add more
columns to the ORDER clause.

There are cases where "dynamic SQL" isn't an option,
such as in functions, or you may just not /like/
the stuff.

You could write several different versions of the 
entire SELECT statement, and switch to using 
whichever one is required - but the more variations
that you want to have, the more distinct SQL
statements would be needed - "geometrically".

You can put ugly statement logic into a view -
but SQL Server is still going to have to execute
that logic in the query (unless it's an 
indexed view).  In my organisation, we've got
some horrible views, and views on views, and then
they wonder why this query runs like a dead
giant turtle...

(In the original example, you could JOIN to a 
table of all the combinations of values of
"sortby" and "sortdirection", but there's no
reason to, except to prove that you can.)

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Puzzling ORDER BY Gene Wirchenko <genew@ocis.net> - 2012-06-21 09:37 -0700
  Re: Puzzling ORDER BY Jeroen Mostert <jmostert@xs4all.nl> - 2012-06-21 21:08 +0200
    Re: Puzzling ORDER BY Gene Wirchenko <genew@ocis.net> - 2012-06-21 13:40 -0700
      Re: Puzzling ORDER BY Jeroen Mostert <jmostert@xs4all.nl> - 2012-06-22 08:24 +0200
        Re: Puzzling ORDER BY Erland Sommarskog <esquel@sommarskog.se> - 2012-06-22 11:31 +0200
    Re: Puzzling ORDER BY Erland Sommarskog <esquel@sommarskog.se> - 2012-06-21 23:31 +0200
      Re: Puzzling ORDER BY Jeroen Mostert <jmostert@xs4all.nl> - 2012-06-22 08:46 +0200
  Re: Puzzling ORDER BY rja.carnegie@gmail.com - 2012-07-02 03:36 -0700

csiph-web