-
June 6th, 2001, 11:20 PM
#1
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?
-
June 7th, 2001, 03:44 AM
#2
Registered User
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...
-
June 7th, 2001, 07:18 AM
#3
Tried changing the datatype to a varchar before posting the message. I should have mentioned that. But it comes up with the same error.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|
Bookmarks