September 10, 2004 at 12:57 pm
I am having a problem using a dynamic order by statement in a procedure. The procedure follows.
This lists employment anniversary dates and should be sortable by first name, month of anniversary or total number of years. This works fine for Month and Year sorting, but when I try to sort by name, I get an error msg. "Syntax error converting the varchar value 'Joe ' to a column of data type int."
Any ideas on how I can fix this?
CREATE Procedure p_SelectAnniversaries_All
(
@SortBy char(10) = 'Month'
)
AS
SELECT NAME = FirstName, LastName,
Years =
CASE WHEN DateDiff(mm, HireDate, GetDate()) < 12 THEN (RTrim(CAST(DateDiff(mm, HireDate, GetDate()) As Char)) + ' mos')
ELSE CAST((DateDiff(mm, HireDate, GetDate()) / 12)AS Char)
END,
DateName(MM,HireDate) + ' ' + CAST(DatePart(YY, HireDate) As Char) As ADate
FROM Primary_Directory
WHERE Display=1 AND Active = 1 AND HireDate IS NOT NULL
ORDER BY CASE
WHEN @SortBy='Name' Then FirstName
WHEN @SortBy='Years' Then DateDiff(mm, HireDate, GetDate())
WHEN @SortBy='Month' Then CAST(DatePart(mm, HireDate) AS Char)
END
September 10, 2004 at 3:27 pm
Although this may not be a perfect solution - you can use the following as a workaround.
I have tested it out in the Northwind database and used the Employees table.
declare @SortBy char(10)
select @sortBy = 'Name'
SELECT FirstName, LastName,
Years =
CASE WHEN DateDiff(mm, HireDate, GetDate()) < 12 THEN (RTrim(CAST(DateDiff(mm, HireDate, GetDate()) As Char)) + ' mos')
ELSE CAST((DateDiff(mm, HireDate, GetDate()) / 12)AS Char)
END,
DateName(MM,HireDate) + ' ' + CAST(DatePart(YY, HireDate) As Char) As ADate
FROM Employees
WHERE HireDate IS NOT NULL
ORDER BY CASE
WHEN @SortBy='Name' Then ASCII(FirstName)
WHEN @SortBy='Years' Then DateDiff(mm, HireDate, GetDate())
WHEN @SortBy='Month' Then CAST(DatePart(mm, HireDate) AS Char)
END
September 10, 2004 at 5:46 pm
I believe the ASCII function produces values based on the first character (at least it did on my test server). Try this:
use Northwind
declare @SortBy char(10)
select @sortBy = 'Name'
SELECT ASCII(FirstName) AS ASCIIValue, FirstName, LastName,
Years = CASE WHEN DateDiff(mm, HireDate, GetDate()) < 12 THEN (RTrim(CAST(DateDiff(mm, HireDate, GetDate()) As Char)) + ' mos')
ELSE CAST((DateDiff(mm, HireDate, GetDate()) / 12)AS Char)
END,
DateName(MM,HireDate) + ' ' + CAST(DatePart(YY, HireDate) As Char) As ADate
FROM Employees
WHERE HireDate IS NOT NULL
ORDER BY CASE
-- WHEN @SortBy='Name' Then ASCII(FirstName)
WHEN @SortBy = 'Name' THEN FirstName
WHEN @SortBy='Years' Then CONVERT(varchar(50),DateDiff(mm, HireDate, GetDate()))
WHEN @SortBy='Month' Then CONVERT(varchar(50),CAST(DatePart(mm, HireDate) AS Char))
END
Everett Wilson
ewilson10@yahoo.com
September 13, 2004 at 6:23 am
Thanks for the help. Neither of these worked. Here's why
If years is char, or varchar the sort is alphanum. So I get results like this.
1
11
12
2
3
I found that if I CAST the 'Years' to INT it works fine. I don't understand why. I would appreciate it if someone could enlighten me.
Thanks
September 13, 2004 at 9:35 am
Hmm, didn't even think of checking the Year and Month order by part of your statement but yeah, string sorting is performed one character at a time with blank (or no more characters) coming before a number and letters coming after numbers (and special characters such as #,%,& before numbers but after blanks).
Everett Wilson
ewilson10@yahoo.com
August 18, 2006 at 8:10 am
I know this thread is quite old, but I had the same problem today, and after some head scratching, and Google searching, came up with a better solution and the answer to why the error occurs in the first place.
What you want to do is split the CASE clause into separate statements:
ORDER BY
CASE WHEN @SortBy='Name' Then FirstName END,
CASE WHEN @SortBy='Years' Then DateDiff(mm, HireDate, GetDate()) END,
CASE WHEN @SortBy='Month' Then CAST(DatePart(mm, HireDate) AS Char) END
The reason is to do with data type precedence; search BOL or read:
http://www.extremeexperts.com/SQL/Articles/CASEinORDER.aspx
for more info.
August 18, 2006 at 8:29 am
Or just
use Northwind
declare @SortBy char(10)
select @sortBy = 'Name'
SELECT ASCII(FirstName) AS ASCIIValue, FirstName, LastName,
Years = CASE WHEN DateDiff(mm, HireDate, GetDate()) < 12 THEN (RTrim(CAST(DateDiff(mm, HireDate, GetDate()) As Char)) + ' mos')
ELSE CAST((DateDiff(mm, HireDate, GetDate()) / 12)AS Char)
END,
DateName(MM,HireDate) + ' ' + CAST(DatePart(YY, HireDate) As Char) As ADate
FROM Employees
WHERE HireDate IS NOT NULL
ORDER BY CASE
-- WHEN @SortBy='Name' Then ASCII(FirstName)
WHEN @SortBy = 'Name' THEN FirstName
WHEN @SortBy='Years' Then right(space(50) + CONVERT(varchar(50), DateDiff(mm, HireDate, GetDate())), 50)
WHEN @SortBy='Month' Then right(space(50) + CONVERT(varchar(50), DatePart(mm, HireDate)), 50)
END
N 56°04'39.16"
E 12°55'05.25"
August 18, 2006 at 1:42 pm
The best way to do this is to eliminate the param in the orderby...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply