使用CASE ..何时进行'动态'排序 [英] Using CASE .. WHEN to have 'dynamic' sort
问题描述
我正在尝试创建一个返回记录集的存储过程，但是我想要能够选择ORDER的
的mijn参数列表中的BY子句存储过程。由于CASE .. WHEN只能在SELECT
条款中使用，我想出了以下内容：
- BEGIN SCRIPT -
DECLARE @blah AS VARCHAR（20）
SET @blah =''DOSSIER_CODE''
SELECT DOC_PID，SCAN_DATE，DOC_STATE，isViewed，DOC_COMMENT，
requestDelete，USER_FNAME，USER_NAME，DOSSIER_CODE，COUNT（NOTE_PID）
NrOfNotes，
CASE @blah
WHEN' 'DOSSIER_CODE''
那么DOSSIER_CODE
当'SCAN_DATE''
那么SCAN_DATE
ELSE
SCAN_DATE
结束ORDERFIELD
来自MR_DOCS
LEFT OUTER JOIN MR_USERS
ON MR_DOCS。 USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE MR_DOCS.USER_FID = 1
AND DOC_STATE IN（1,3,4）
AND REMINDER_DATE< = getdate（ ）<无线电通信/>
AND MR_DOCS.isVisible = 1
AND TREE_FID为空
- 由Tim Derdelinckx添加 - 2005.06.20
AND TODO_FID为空
- 选择为此用户扫描的文档（1），
- 或移至此用户（3），
- 或转发给该用户（4），
GROUP BY DOC_PID，SCAN_DATE，DOC_STATE，isViewed，DOC_COMMENT，
requestDelete，USER_FNAME，USER_NAME，DOSSIER_CODE
UNION
SELECT DOC_PID，SCAN_DATE，DOC_STATE，isViewed，DOC_COMMENT，
requestDelete，USER_FNAME，USER_NAME，DOSSIER_CODE，COUNT（NOTE_PID）
NrOfNotes，
CASE @blah
WHEN''DOSSIER_CODE''
那么DOSSIER_CODE
当'SCAN_DATE''
那么SCAN_DATE
ELSE
SCAN_DATE
END as ORDERFIELD
来自MR_DOCS
LEFT OUTER JOIN MR_USERS
ON USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON d OSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHOR BORROW_USER_FID = 1
AND DOC_STATE = 5
AND REMINDER_DATE< = getdate（）
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
- 由Tim Derdelinckx添加 - 2005.06.20
和TODO_FID为空
- 或借给该用户
GROUP BY DOC_PID，SCAN_DATE，DOC_STATE，isViewed，DOC_COMMENT，
requestDelete，USER_FNAME，USER_NAME，DOSSIER_CODE
ORDER BY ORDERFIELD DESC
- END SCRIPT - -
但它似乎无法正常工作：
当SET @blah =''SCAN_DATE''时，它运行得很好！
当SET @blah =''DOSSIER_CODE''：
我收到错误：服务器：消息242，级别16，状态3，行3
将char数据类型转换为datetime数据类型导致
超出范围的datetime值。
警告：通过汇总或其他SET
操作消除空值。
任何有关此事的想法？或者也许是另一种处理方式（不是
with CASE .. WHEN）？
非常感谢，
Tim @ Allgeier
***通过开发人员指南 http：// www发送.developersdex.com ***
Jozef de Veuster（no****@devdex.com）写道：我正在尝试创建一个返回记录集的存储过程，但我希望能够在存储过程的mijn参数列表中选择ORDER BY子句。由于CASE .. WHEN只能在SELECT
条款中使用，我想出了以下内容：
Ehum，你可以这样说：
SELECT ...
FROM ....
按案例订购@blah ....结束
案例@blah
什么时候'DOSSIER_CODE''
那么DOSSIER_CODE
什么时候'SCAN_DATE''
那么SCAN_DATE
ELSE
SCAN_DATE
END作为ORDERFIELD
当SET @blah =''DOSSIER_CODE''：
我收到一个错误：服务器：Msg 242，Level 16，State 3，Line 3
转换对日期时间数据类型的char数据类型导致了超出范围的日期时间值。
警告：通过聚合或其他SET
操作消除空值。
任何有关此事的想法？或者也许是另一种处理方式（不是用CASE .. WHEN）？
CASE表达式总是返回相同的数据类型。如果不同的
分支具有不同的数据类型，它们将根据数据类型优先顺序转换为
，这在联机丛书中描述
数据类型在T-SQL参考中。在这种情况下，varchar比datetime具有更低的优先级，因此SQL Server会尝试将varchar
列转换为datetime。
这可以通过两种方式解决。一个是为日期列添加显式转换
：
convert（varchar，SCAN_DATE，121）
（表格121是YYYY-MM-DD HH：MM：SS.fff）
另外两个有多个列：
ORDER BY CASE @blah WHEN''DOSSIER_CODE''那么DOSSIER_CODE结束，
CASE @blan WHEN''DOSSIER_CODE''那么空
ELSE SCAN_DATE
结束
-
Erland Sommarskog，SQL Server MVP， es ****@sommarskog.se
SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/books.asp
好的，非常感谢Erland！
我找不到任何关于使用CASE的信息..当时在
Transact SQL，因此......
谢谢，
Tim @ Allgeier
***通过Developersdex发送 http://www.developersdex.com ***
为了好玩，我在ORDER BY中使用CASE..WHEN尝试了你的例子，但是
我收到错误：
服务器：Msg 104，Level 15，State 1，Line 3
ORDER BY项必须出现在select中列表如果语句包含
a UNION运算符。
看起来CASE语句的结果有点转换，所以
SQLServer无法识别该字段已在SELECT列表中。 （I
尝试使用DOSSIER_CODE列，没有CAST或CONVERT）
Tim @ Allgeier
** *通过开发人员指南 http://www.developersdex.com 发送***
Hi,
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:
-- BEGIN SCRIPT --
DECLARE @blah AS VARCHAR(20)
SET @blah = ''DOSSIER_CODE''
SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @blah
WHEN ''DOSSIER_CODE''
THEN DOSSIER_CODE
WHEN ''SCAN_DATE''
THEN SCAN_DATE
ELSE
SCAN_DATE
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON MR_DOCS.USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE MR_DOCS.USER_FID = 1
AND DOC_STATE IN (1, 3, 4)
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
-- Added by Tim Derdelinckx - 2005.06.20
AND TODO_FID IS NULL
-- Select documents that are scanned for this user (1),
-- or moved to this user (3),
-- or forwarded to this user (4),
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
UNION
SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @blah
WHEN ''DOSSIER_CODE''
THEN DOSSIER_CODE
WHEN ''SCAN_DATE''
THEN SCAN_DATE
ELSE
SCAN_DATE
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE BORROW_USER_FID = 1
AND DOC_STATE = 5
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
-- Added by Tim Derdelinckx - 2005.06.20
AND TODO_FID IS NULL
-- or borrowed to this user
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
ORDER BY ORDERFIELD DESC
-- END SCRIPT --
But it doesn''t seem to work correctly:
When SET @blah = ''SCAN_DATE'', it works just fine!
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)?
Thanks a lot,
Tim@Allgeier
*** Sent via Developersdex http://www.developersdex.com ***
Jozef de Veuster (no****@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, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Okay, Thanks a lot Erland!
I couldn''t find anything about using CASE .. WHEN in the ORDER BY in the
Transact SQL, therefore ...
Thanks,
Tim@Allgeier
*** Sent via Developersdex http://www.developersdex.com ***
Just for fun I tried your example using CASE..WHEN in the ORDER BY, but
I get the error:
Server: Msg 104, Level 15, State 1, Line 3
ORDER BY items must appear in the select list if the statement contains
a UNION operator.
It seems like the result of the CASE statement is a bit transformed, so
SQLServer doesn''t recognize that field is already in the SELECT list. (I
tried with the DOSSIER_CODE column, without CAST or CONVERT)
Tim@Allgeier
*** Sent via Developersdex http://www.developersdex.com ***
这篇关于使用CASE ..何时进行'动态'排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
