sql - ORDER BY depending on parameter results in error -

i have stored procedure initiates order depending on parameter:

drop procedure [dbo].[getusersbyclusterandusername]   go create procedure [dbo].[getusersbyclusterandusername]     @sortfield [nvarchar] (256) = 'username',     @sortorder [int] = 0     select * [user]      order     case when @sortorder = 0         case              when @sortfield = 'username' user_username              when @sortfield = 'lastlogindate' user_lastlogindate              when @sortfield = 'creationdate' user_creationdate end     end asc,         case when @sortorder = 1         case             when @sortfield = 'username' user_username              when @sortfield = 'lastlogindate' user_lastlogindate             when @sortfield = 'creationdate' [user_creationdate] end     end desc return 0 go 

however... if call procedure this:

exec dbo.getusersbyclusterandusername @sortorder=1, @sortfield='username' 

i following error:

msg 241, level 16, state 1, procedure getusersbyclusterandusername, line 7 conversion failed when converting date and/or time character string. 

why try convert date/time. can please help?

the problem type conversion case. when using order by in way, use multiple case statements:

order (case when @sortorder = 0 , @sortfield = 'username' user_username end),          (case when @sortorder = 0 , @sortfield = 'user_lastlogindate' user_lastlogindate end),          (case when @sortorder = 0 , @sortfield = 'user_creationdate' user_creationdate end),          (case when @sortorder = 1 , @sortfield = 'username' user_username end) desc,          (case when @sortorder = 1 , @sortfield = 'user_lastlogindate' user_lastlogindate end) desc,          (case when @sortorder = 1 , @sortfield = 'user_creationdate' user_creationdate end) desc 

the problem case has single output type, determined when query compiled. type based on logic combining types form then clause. so, result each then clause converted overall type -- , error occurs.

you can read data precedence rules here. solution simple: use multiple case statements.