---

29/04/2021- duocnt    943 Views    

---

Basic structure.

create Function [Functionname]  
(  
       @Paramter nvarchar(20)  
)  
Returns numeric(32,6) 
AS BEGIN  
       Declare @ReturnValue numeric(32,6)  
       --T-SQL here to retrieve value for @ReturnValue
 return @ReturnValue
END

Example.

create Function Royalty  
(  
 @INVOICEID nvarchar(20)  
)  
Returns numeric(32,6) 
AS BEGIN  
Declare @ReturnCost numeric(32,6) 
 
 Declare @CostHeader numeric(32,6) 
 Declare @LineCount int  
 
 set @CostHeader = (select TOP(1) ROYALTY FROM CustInvoiceJourDataStaging a where a.INVOICEID = @INVOICEID order by a.ROYALTY desc)  
 set @LineCount = (Select COUNT(*) from CustInvoiceTransStaging a where a.INVOICEID = @INVOICEID) 
 Set @ReturnCost = @CostHeader / @LineCount  
 
 return @ReturnCost  
END  

Usage

select   
 b.INVOICEDATE,  
 b.INVOICEID,  
 b.INVOICINGNAME,  
 a.NETTURNOVERDKK,  
 a.NETTURNOVERUSD,   
 a.RANGE,  
 dbo.Royalty(b.INVOICEID) as 'Royalty', 
 dbo.Cost(b.INVOICEID) as 'Cost'  
from CustInvoiceTransStaging a  
left join
CustInvoiceJourDataStaging b on a.INVOICEID = b.INVOICEID  
where b.DATAAREAID = 'cog' and b.INVOICINGNAME !=''