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 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!
The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list.
Using Group By and Order By TogetherGROUP BY goes before the ORDER BY statement because the latter operates on the final result of the query.
As of SQL standarts, in this case ORDER BY must influence only results of GROUP BY , not data before grouping.
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)
This should give you the sort you want, as it will format the date string yyyy-mm-dd hh:mm:ss.
If you care about performance, you might want a different approach: 1. Wrap your select in an inline TVF 2. Use two different SELECTs, so that they can get two different plans, potentially more efficient that the generic one-size-fits-all one plan you are getting now:
IF @SortBy='product' BEGIN
SELECT AlertID,
(snip)
FROM MyTvf
ORDER BY Description ;
RETURN @@ERROR ;
END
IF @SortBy='Vendor' BEGIN
SELECT AlertID,
(snip)
FROM MyTvf
ORDER BY LongName ;
RETURN @@ERROR ;
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With