union sql + order in a stored procedure

[webmast3r]

hello: i am creating a stored procedure that will select fields from 2 tables, and then order the resulting set according to the fields index. i.e. 17th field in the record (discountedcost)

the weird thing is:

if i use the select sql directly from vb6.

dim rs as new adodb.recordset
rs.open sql, db, adopenforwardonly, adlockreadonly

it works perfectly

but if i create the stored procedure and run it in ISQL no sorting is applied...

any hints??


here's the sql variable in vb:

sql = "select product_offers.prodnum as prodnum, product_offers.punit as punit, 'Offer' as type, product_offers.id as id, product_offers.offertype as offertype, " & _
"product_offers.pqtytype, products.descript as prodname1, product_offers.fprodnum as fprodnum, " & _
"units.descript as unitname1, product_offers.pqty as pqty, product_offers.fqty as fqty, " & _
"product_offers.funit as funit, product_offers.dunitcost*currencies.conversion as dunitcost, product_offers.pcost as pcost, " & _
"currencies.descript as curname, suppliers.name as supname, ((product_offers.dunitcost*currencies.conversion)*((100-isnull(suppliers.billdiscount,0))/100)*((100-isnull(suppliers.eomdiscount,0))/100)) as discountedcost, currencies.id as curid " & _
"from product_offers, currencies, suppliers, units, products "
sql = sql & "where product_offers.prodnum=products.prodnum and product_offers.supplierid=suppliers.id and product_offers.punit=units.id and suppliers.currencycode=currencies.id and product_offers.prodnum=" & prodnum.Text

sql = sql & " union " & _
"select product_costs.prodnum as prodnum, product_costs.punit as punit, 'Cost' as type, product_costs.id as id, 0 as offertype, " & _
"1 as pqtytype, products.descript as prodname1, 0 as fprodnum, " & _
"units.descript as unitname1, 1 as pqty, 0 as fqty, " & _
"0 as funit, product_costs.dunitcost*currencies.conversion as dunitcost, product_costs.pcost as pcost, " & _
"currencies.descript as curname, suppliers.name as supname, ((product_costs.dunitcost*currencies.conversion)*((100-isnull(suppliers.billdiscount,0))/100)*((100-isnull(suppliers.eomdiscount,0))/100)) as discountedcost, currencies.id as curid " & _
"from product_costs, currencies, suppliers, units, products "
sql = sql & "where product_costs.supplierid=suppliers.id and product_costs.prodnum=products.prodnum and product_costs.punit=units.id and suppliers.currencycode=currencies.id and product_costs.prodnum=" & prodnum.Text

sql = sql & " Order by " & orderfield & " asc"

and here's the stored procedure

create procedure "dba".GetProductOffersForAllSuppliers(in @prodnum integer,in @orderfield integer)
result(prodnum integer,punit integer,otype char(5),id integer,offertype smallint,pqtytype smallint,prodname1 varchar(50),fprodnum integer,unitname1 varchar(20),pqty decimal(30,6),fqty decimal(30,6),funit integer,dunitcost float(8),pcost decimal(19,4),curname varchar(15),supname varchar(30),disccost float(8),curid integer) begin
  select product_offers.prodnum as prodnum,product_offers.punit as punit,'Offer' as otype,product_offers.id as id,
    product_offers.offertype as offertype,product_offers.pqtytype,products.descript as prodname1,
    product_offers.fprodnum as fprodnum,units.descript as unitname1,product_offers.pqty as pqty,
    product_offers.fqty as fqty,product_offers.funit as funit,product_offers.dunitcost*currencies.conversion as dunitcost,
    product_offers.pcost as pcost,currencies.descript as curname,suppliers."name" as supname,
    ((product_offers.dunitcost*currencies.conversion)*((100-isnull(suppliers.billdiscount,0))/100)*((100-isnull(suppliers.eomdiscount,0))/100)) as disccost,
    currencies.id as curid
    from product_offers,currencies,suppliers,units,products
    where product_offers.prodnum=products.prodnum and product_offers.supplierid=suppliers.id and product_offers.punit=units.id and suppliers.currencycode=currencies.id and product_offers.prodnum=@prodnum
  union select product_costs.prodnum as prodnum,product_costs.punit as punit,'Cost' as otype,product_costs.id as id,
    0 as offertype,1 as pqtytype,products.descript as prodname1,0 as fprodnum,units.descript as unitname1,1 as pqty,
    0 as fqty,0 as funit,product_costs.dunitcost*currencies.conversion as dunitcost,product_costs.pcost as pcost,
    currencies.descript as curname,suppliers."name" as supname,
    ((product_costs.dunitcost*currencies.conversion)*((100-isnull(suppliers.billdiscount,0))/100)*((100-isnull(suppliers.eomdiscount,0))/100)) as disccost,
    currencies.id as curid
    from product_costs,currencies,suppliers,units,products
    where product_costs.supplierid=suppliers.id and product_costs.prodnum=products.prodnum and product_costs.punit=units.id and suppliers.currencycode=currencies.id and product_costs.prodnum=@prodnum order by
    @orderfield desc
end