Dynamic 'order by' in a stored procedure
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?
