Dynamic 'order by' in a stored procedure

[lysergic]

Have a field name from a drop down that I am using to try to dynamicaly sort in a stored procedure for a recordset. Here is the stored procedure

CREATE PROCEDURE spGetOrders

@CustomerID int,
@save int,
@sname varchar(55),
@scriteria varchar(55),
@statesearch varchar(55),
@countysearch varchar(55),
@sort1 int,
@sort2 int

AS

if @scriteria='lastname' and @statesearch='0'
begin

select
a.ID,
a.SearchState,
a.SearchCounty,
a.DOBMM,
a.DOBDD,
a.DOBYYYY,
'SS1' = substring(a.SSNum,1,3),
'SS2' = substring(a.SSNum,4,2),
'SS3' = substring(a.SSNum,6,4),
a.Result,
b.FirstName,
b.LastName,
b.MiddleName,
b.Suffix

from orders a, ordername b
where customerid=@customerid
and a.id=b.ordersid
and b.LastName like @sname+'%'
order by case @sort1
when 1 then a.ID
when 2 then a.SearchState
when 3 then a.SearchCounty
when 4 then b.LastName
when 5 then b.FirstName
when 6 then b.MiddleName
when 7 then a.DOBYYYY
when 8 then a.SSNum
when 9 then a.Result
end
end

It compiles in sql without any errors and it runs IF the column contains data that is only integers (id, SSNum, ect). But if the column that I am trying to sort it with contains characters (results, state, etc) I get the following error on my asp page:

Syntax error converting the varchar value 'xxx' to a column of data type int.

Any ideas?

[Higg]

Originally posted by EtOH:
<STRONG>
@sort1 int,
@sort2 int
</STRONG>

Isn't it so, that you defined the sorts as ints? I'd give a tip to that as your problem...

[lysergic]

Tried changing the datatype to a varchar before posting the message. I should have mentioned that. But it comes up with the same error.