Why do you need to return a table from a tax function? It seems to me that all calculations could be be done inside it.
Also you can use calculated columns in consequent calculations by employing derived tables
SELECT *, s.price + disc as netprice
FROM (
select [lineno],s.item,descrip,qtyord,qtyshp,cost,
(((s.price*(disc/100))+(s.price*(specialdisc/100))+(s.price*(termdisc/100))+disc2+disc3)*-1) as disc,
s.price,extprice,icitem.sunmsid
from pkpro..sotran as s,
JOIN icitem ON s.item = icitem.item
where sono = @sono
) dt1
>I'm trying to select a bunch of information including lineno, and I want to pass lienno into a function but its keeps giving me an incorrect syntax error. Basically,
>
>select [lineno],(select item from tax([lineno])) from table
>
>cant seem to get this to work, I think my syntac might be off.
>
>CREATE PROCEDURE sp_invoicedetail
>@sono as char(10)
>AS
>select [lineno],s.item,descrip,qtyord,qtyshp,cost,
>(((s.price*(disc/100))+(s.price*(specialdisc/100))+(s.price*(termdisc/100))+disc2+disc3)*-1) as disc,
>--DJS tried using the disc name first but didnt work
>s.price + (((s.price*(disc/100))+(s.price*(specialdisc/100))+(s.price*(termdisc/100))+disc2+disc3)*-1) as netprice,
>--(select ((qtyshp1*extprice1)+(qtyshp2*extprice2)+(qtyshp3*extprice3)) from taxitem(@sono,[lineno])) as tax,
>s.price,extprice,icitem.sunmsid
>from pkpro..sotran as s,
>icitem
>where sono = @sono
>and s.item = icitem.item
>GO
--sb--