Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-11 : 08:32:49
|
Phillip writes "I am using a SQLServer7.0 stored procedure to return a record set to a web page.
I am using your 8-30-99 SQL CASE suggestion to give the user dynamic sorting capability. i.e. from the code.
CREATE PROCEDURE ppl_style_resistor @pgroup varchar(25), @family varchar(25), @member varchar(25), @viewvalue varchar(25), @sort int AS
SELECT PPL_PART.part_number, PPL_PART.part_description,
PPL_PART.value, PPL_PART.tolerance, PPL_PART.wvdc,
PPL_PART.usage, BRP_PART.INV_QTY, BRP_PART.UNIT_COST,
PPL_PART.spec,
9 fieldcount, "Part Number" fieldname1, "Description" fieldname2,
"Value" fieldname3, "Tolerance" fieldname4, "Voltage" fieldname5,
"Use" fieldname6, "Inv Qty" fieldname7, "Cost" fieldname8, "Specification" fieldname9,
CASE
when @sort = 1 then ppl_part.part_number
when @sort = 2 then ppl_part.part_description
when @sort = 3 then ppl_part.value
when @sort = 4 then ppl_part.tolerance
when @sort = 5 then ppl_part.wvdc
when @sort = 6 then ppl_part.usage
when @sort = 7 then brp_part.inv_qty
when @sort = 8 then brp_part.unit_cost
when @sort = 9 then ppl_part.spec
else ppl_part.value
END AS SortCol
FROM PPL_PART INNER JOIN
BRP_PART ON
PPL_PART.part_number = BRP_PART.PARTNO
WHERE (PPL_PART.pgroup = @pgroup)
AND (PPL_PART.family = @family)
AND (PPL_PART.member = @member)
AND (PPL_PART.style = @viewvalue)
order by SortCol
My problem is, this only sorts the resulting record set when the column is a numeric. For all my varchar column, the query returns the following error:
Error converting data type varchar to numeric.
Any suggestions on how to make my non-numeric columns sort using the CASE technique.
Any help would be greatly appreciated.
Sincerely,
Phillip Farr" |
|
|
|
|
|
|
|
Parameterized Order By's from a web application