Msg 241: Syntax Error Converting Datetime From Character String

Hi,

I'm having a strange problem with the following stored proc:


Code:

sql

declare @orderBy tinyint

set @orderBy = 2

SELECT tblUsers.userID, firstName, lastName, dateRegistered, count(movieSessionID) As sessionsCount FROM tblUsers
LEFT JOIN tblMovieSessions on tblUsers.userID = tblmovieSessions.userID
Group By tblUsers.userID, firstName, lastName, dateRegistered
Order By Case
WHEN @orderBy = 2 THEN firstName
WHEN @orderBy = 3 THEN dateRegistered
WHEN @orderBy = 4 THEN count(movieSessionID)
else lastName
End desc



When I execute it with @orderBy = 1 or 2 I get the following error message: Quote: Server: Msg 241, Level 16, State 1, Line 5
Syntax error converting datetime from character string.

However, with @orderBy = 3 or 4 it executes successfully.

I don't understand why this change should have this impact because the error seems to be occuring on the first line (i.e. SELECT...).

If I take the dateRegistered column out of the query I get a new error message:

Quote:
Server: Msg 245, Level 16, State 1, Line 5
Syntax error converting the nvarchar value 'Adam' to a column of data type int.

I don't understand why it is trying to convert these columns during the query anyway, let alone how to solve the problem.

Anyone got any ideas?