代码之家 › 专栏 › 技术社区 › samuel

SQL转换失败错误

sql-order-by sql-server sql

samuel · 技术社区 · 16 年前

调用此存储过程时:

ALTER PROCEDURE [dbo].[GetSorted]   
(
   @OrderByColumn   nvarchar(256)

)
AS
SET NOCOUNT ON      

   SELECT itDocs.AddedDate, itDocs.AddedBy  FROM itDocs      
    ORDER BY 
             CASE WHEN @OrderByColumn='AddedDate' THEN itDocs.AddedDate
                  WHEN @OrderByColumn='AddedBy' THEN itDocs.AddedBy 

        END  ASC

我得到错误:

转换日期时转换失败和/或字符串中的时间

我就是这样称呼SP的:

DECLARE @return_value int

EXEC    @return_value = [dbo].[GetSorted]

        @OrderByColumn = 'AddedBy'


SELECT  'Return Value' = @return_value

GO

更新: 如果我使用前两个答案中建议的多案例方法, 尝试添加另一个定向案例时出错:

  ORDER BY  
        CASE WHEN @OrderDirection=0 THEN            
           CASE WHEN @OrderByColumn='AddedDate' THEN itDocs.AddedDate END ASC,
           CASE WHEN @OrderByColumn='AddedBy' THEN   itDocs.AddedBy END ASC    
        end       
       CASE WHEN @OrderDirection=1 THEN

2 回复 | 直到 16 年前

Tim 16 年前

这里有一篇很好的文章解释了您收到的错误:

Using CASE in ORDER BY clause

您可以将存储过程更改为以下内容,以允许ORDER BY子句中的case工作(现在已编辑为将ORDER DIRECTION包含为参数):

ALTER PROCEDURE [dbo].[GetSorted]   
(
    @OrderByColumn   nvarchar(256),
    @OrderDirection  int
)
AS
SET NOCOUNT ON             
   SELECT itDocs.AddedDate, itDocs.AddedBy FROM itDocs          
      ORDER BY              
         CASE WHEN @OrderByColumn='AddedDate' AND @OrderDirection=0 THEN itDocs.AddedDate END ASC,
         CASE WHEN @OrderByColumn='AddedBy' AND @OrderDirection=0 THEN itDocs.AddedBy END ASC,         
         CASE WHEN @OrderByColumn='AddedDate' AND @OrderDirection=1 THEN itDocs.AddedDate END DESC,
         CASE WHEN @OrderByColumn='AddedBy' AND @OrderDirection=1 THEN itDocs.AddedBy END DESC

gbn 16 年前

您正在尝试转换数据类型,而datetime具有更高的优先级。基本上“addedby='bob'”无法转换…

SELECT
    itDocs.AddedDate, itDocs.AddedBy
FROM
    itDocs      
ORDER BY
    CASE OrderByColumn WHEN 'AddedDate' THEN itDocs.AddedDate ELSE '19000101' END,
    CASE OrderByColumn WHEN 'AddedBy' THEN itDocs.AddedBy ELSE '' END

推荐文章