CASE Issue

When I set @.reorderfav = 1 or @.reorderfav = 3, it works perfect. However,
when i set @.reorderfav = 2, it throws this error:
Server: Msg 241, Level 16, State 1, Line 7
Syntax error converting datetime from character string.
I have also put the ProjectMain.ProjectName in the ORDER BY and ran it
manually, and it works. This is why I'm so . Any help would be
greatly appreciated.
Thanks,
JJ
CREATE PROCEDURE [usp_getindexFav]
(
@.userid varchar(6),
@.reorderfav int
)
AS
SELECT AppUserPref.UserID, ProjectMain.ProjectName,
ProjectMain.ProjectID, ProjectMain.CreatedPostDate
FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
ProjectMain.ProjectID
WHERE AppUserPref.ID = @.userid
ORDER BY CASE
WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
ELSE ProjectMain.ProjectName
END
GO
Manually (this works):
SELECT AppUserPref.UserID, ProjectMain.ProjectName,
ProjectMain.ProjectID, ProjectMain.CreatedPostDate
FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
ProjectMain.ProjectID
WHERE AppUserPref.ID = @.userid
ORDER BY ProjectMain.ProjectNameCASE is an expression that can only return a value of the same data type.
You will need to break it out so that each potential data type is handled by
a separate CASE expression.
http://www.aspfaq.com/2501
http://www.aspfaq.com/
(Reverse address to reply.)
"Josh Jones" <myaddress@.thidress.com> wrote in message
news:uhvG0RrGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> When I set @.reorderfav = 1 or @.reorderfav = 3, it works perfect. However,
> when i set @.reorderfav = 2, it throws this error:
> Server: Msg 241, Level 16, State 1, Line 7
> Syntax error converting datetime from character string.
> I have also put the ProjectMain.ProjectName in the ORDER BY and ran it
> manually, and it works. This is why I'm so . Any help would be
> greatly appreciated.
> Thanks,
> JJ
> CREATE PROCEDURE [usp_getindexFav]
> (
> @.userid varchar(6),
> @.reorderfav int
> )
> AS
>
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY CASE
> WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
> WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
> WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
> ELSE ProjectMain.ProjectName
> END
> GO
>
> Manually (this works):
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY ProjectMain.ProjectName
>|||"Josh Jones" <myaddress@.thidress.com> wrote in message
news:uhvG0RrGFHA.3724@.TK2MSFTNGP10.phx.gbl...

> CREATE PROCEDURE [usp_getindexFav]
> (
> @.userid varchar(6),
> @.reorderfav int
> )
> AS
>
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY CASE
> WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
> WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
> WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
> ELSE ProjectMain.ProjectName
> END
Case requires all output values to be of the same type and tries to convert
if not. Since projectName can't be converted to a date it breaks. Try this
for the order by
ORDER BY
CASE WHEN @.reorderfav = 1 then ProjectMain.ProjectID END,
CASE WHEN @.reorderfav = 2 then ProjectMain.ProjectName END,
CASE WHEN @.reorderfav = 3 then ProjectMain.CreatedPostDate END,
ProjectMain.ProjectName
Good Luck,
Jim|||Jim--good quick answer!!
Thanks!!!
"Josh Jones" <myaddress@.thidress.com> wrote in message
news:uhvG0RrGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> When I set @.reorderfav = 1 or @.reorderfav = 3, it works perfect. However,
> when i set @.reorderfav = 2, it throws this error:
> Server: Msg 241, Level 16, State 1, Line 7
> Syntax error converting datetime from character string.
> I have also put the ProjectMain.ProjectName in the ORDER BY and ran it
> manually, and it works. This is why I'm so . Any help would be
> greatly appreciated.
> Thanks,
> JJ
> CREATE PROCEDURE [usp_getindexFav]
> (
> @.userid varchar(6),
> @.reorderfav int
> )
> AS
>
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY CASE
> WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
> WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
> WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
> ELSE ProjectMain.ProjectName
> END
> GO
>
> Manually (this works):
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY ProjectMain.ProjectName
>