SQL Server 条件排序依据 [英] SQL Server Conditional Order By
问题描述
我在 SQL Server 2005 中有一个 SQL 查询,当我包含条件 order by 时,该查询会中断.当我删除订单时,查询有效.当我按条件明确编写订单时(例如按 p.Description 订购),它会起作用.当我包含条件 order by 时,出现错误,
I have a SQL query in SQL Server 2005 that is breaking when I include a conditional order by. When I remove the order by, the query works. When I explicitly write the order by condition (e.g. order by p.Description) it works. When I include the conditional order by, I get the error,
'Conversion failed when converting character string to smalldatetime data type'
SQL Server 没有显示是哪行代码导致了这个错误.我想知道如何解决这个问题,以便我可以使用条件顺序或排查转换失败的列.
SQL Server isn't showing me which line of code caused this error. I'm wondering how I can fix this so I can use the conditional order by or troubleshoot which column is failing in the conversion.
declare @SearchTerm nvarchar(255)
declare @SortBy nvarchar(255)
declare @Months int
declare @VendorID int
declare @ProductID int
set @SearchTerm = 'focus'
set @SortBy = 'product'
set @Months = 3
set @VendorID = null
set @ProductID = null
-- This makes it so the @Month will filter by n number of months ago.
declare @PreviousMonths datetime
if @Months is null
begin
set @PreviousMonths = 24
end
else
begin
set @PreviousMonths = DateAdd(month, -@Months, GetDate())
end
select
a.dsAlertID as AlertID,
a.ProductID,
v.VendorID,
p.Description as ProductName,
v.LongName as VendorName,
a.Introduction,
a.Writeup,
a.DateAdded
from
ev_ds_Alerts a
left outer join
tblProducts p on a.ProductID = p.ProductID
left outer join
tblVendors v on v.VendorID = p.VendorID
where
( @SearchTerm is null or ( a.Writeup like '% ' + @SearchTerm + '%' or a.Introduction like '% ' + @SearchTerm + '%') )
and (( @Months is null ) or ( @Months is not null and a.DateAdded >= @PreviousMonths))
and (( @VendorID is null ) or ( @VendorID is not null and v.VendorID = @VendorID ))
and (( @ProductID is null ) or ( @ProductID is not null and p.ProductID = @ProductID ))
order by
case @SortBy
when 'product' then p.Description
when 'vendor' then v.LongName
else a.DateAdded
end
-- order by p.Description or v.LongName works when explicitly writing them out!
推荐答案
根据上一个答案,尝试:
Per the previous answer, try:
order by
case @SortBy
when 'product' then p.Description
when 'vendor' then v.LongName
else convert(VARCHAR(25),a.DateAdded,20)
这应该给你你想要的排序,因为它会格式化日期字符串 yyyy-mm-dd hh:mm:ss.
This should give you the sort you want, as it will format the date string yyyy-mm-dd hh:mm:ss.
这篇关于SQL Server 条件排序依据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!