SQL Server条件顺序依据

发布时间：2020-12-12 16:22:32 所属栏目：MsSql教程来源：网络整理

导读：我在SQL Server 2005中有一个SQL查询,当我包含条件顺序时,它会中断.当我删除订单时,查询有效.当我通过条件明确地写出订单时(例如,通过p.Description订购),它可以工作.当我包含条件顺序时,我得到错误, 'Conversion failed when converting character string to

我在SQL Server 2005中有一个SQL查询,当我包含条件顺序时,它会中断.当我删除订单时,查询有效.当我通过条件明确地写出订单时(例如,通过p.Description订购),它可以工作.当我包含条件顺序时,我得到错误,

'Conversion failed when converting character string to smalldatetime data type'

SQL Server没有向我显示哪行代码导致此错误.我想知道如何解决这个问题,以便我可以使用条件顺序或解决哪一列在转换中失败.

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!

解决方法

根据上一个答案,尝试：

订购
案例@SortBy
当’产品’然后p.Description
当’供应商’然后v.LongName
否则转换(VARCHAR(25),a.DateAdded,20)

这应该给你你想要的排序,因为它将格式化日期字符串yyyy-mm-dd hh：mm：ss.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!