title: SQL Server Conditional Order By
body: <p>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, </p>
  
  <pre><code>'Conversion failed when converting character string to smalldatetime data type'
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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 &gt;= @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!
  </code></pre>