|
|
Posted by Erland Sommarskog on 07/25/05 16:35
Jozef de Veuster (nospam@devdex.com) 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@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|