I have a temporary table with the following fields
InvoiceWFID , InvoiceID, , Seq, num_lev, Approver , SentFor, Status
1 1 1 1 tom APP FWD
2 1 1 2 jill APP PND
3 1 10 1 jack APP PND
I want to write a qurery that uses a user defined function and updates
the values in the 1 row and replaces the Approver and Status that is in
Set TIEWF.Status = (Select Top 1 W.cod_Status From IEWorkflow W
Where W.IExpenseID = TIEWF.IExpenseID
And W.num_Lev = TIEWF.num_lev
And W.num_Seq = 10
And W.cod_Status Not In ('FWD','AWB','FDB') )
from #tmpIEWorkflow TIEWF
Where TIEWF.Status = 'FWD'
But this querry only allows me to update the status not the Approver.
How can I manage this using a user defined function. that might take the
invoiceID and the status FWD and return the name of the Approver and the
status as 'Jack' and 'PND'