Re: Using CASE .. WHEN to have 'dynamic' sort

Jozef de Veuster (nospam@xxxxxxxxxx) writes:
> I'm trying to create a Stored Procedure that returns a recordset, but I
> want to be able to choose the ORDER BY clause in mijn parameter list of
> the Stored Procedure. Since CASE .. WHEN can only be used in the SELECT
> clause, I came up with the following:

Ehum, you can say things like:

SELECT ...
FROM ....
ORDER BY CASE @blah .... END

> CASE @blah
> WHEN 'DOSSIER_CODE'
> THEN DOSSIER_CODE
> WHEN 'SCAN_DATE'
> THEN SCAN_DATE
> ELSE
> SCAN_DATE
> END AS ORDERFIELD
>
> When SET @blah = 'DOSSIER_CODE':
> I get an error: Server: Msg 242, Level 16, State 3, Line 3
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Warning: Null value is eliminated by an aggregate or other SET
> operation.
>
> Anyone any ideas about this? Or maybe another way of handling this (not
> with CASE .. WHEN)?

A CASE expression always returns the same data type. If the different
branches have different data types, they are converted according to
the data-type precendence order, which is described in Books Online under
"datatypes" in the T-SQL Reference. In this case here varchar has lower
precendence than datetime, so SQL Server attempts to convert the varchar
column to datetime.

This can be addressed in two ways. One is to add explicit converts
for the date columns:

convert(varchar, SCAN_DATE, 121)

(form 121 is YYYY-MM-DD HH:MM:SS.fff)

The other is two have more than one sort column:

ORDER BY CASE @blah WHEN 'DOSSIER_CODE' THEN DOSSIER_CODE END,
CASE @blan WHEN 'DOSSIER_CODE' THEN NULL
ELSE SCAN_DATE
END


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
.

Relevant Pages

  • Re: Convert help needed desperately
    ... overflow error converting expression to data type datetime. ... Is my sql server hosed???? ... > the timestamp data type. ...
    (microsoft.public.sqlserver.programming)
  • Re: ADODB Command (Stored Procedure)
    ... I also changed the stored procedure name to "procRecalculate". ... And the data type for TotalCost is? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: between dates with no time consideration
    ... > Columnist, SQL Server Professional ... > declare @start as datetime ... >> only passing the date value back to the stored procedure, ...
    (microsoft.public.sqlserver.programming)
  • Re: DATEFORMAT
    ... Your best bet is to let the parameters to the stored procedure be datetime, i.e., no conversion at all in the ... If you use parameters in the proc, SQL Server will determine selectivity based on the value ... >>Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure truncating parameters?
    ... I guess it's like declaring every parameter as SQL_VARIANT ... and checking data type and length in the procedure. ... I believe the stored procedure ... > SQL Server MVP ...
    (microsoft.public.sqlserver.programming)