CREATE function new_function (@some_param nvarchar(20)='MISSED')                                        
RETURNS TABLE                     
AS                    
RETURN(
select  MAX (LEGAL_NM) AS LEGAL_NAME,
                MAX (TCPRL.VEND_CO_ID) AS VEND_CO_ID, 
                MAX(tcx.max_process_date) as LTST_PROCESSED_CHK_DATE         
FROM t_bps_company_extract TCX  
        INNER JOIN T_COMPANY_PAYROLL TCPRL 
        ON TCPRL.Vend_CO_ID=TCX.VEND_CO_ID_TEXT  
WHERE TCX.Company_status = 'Active'  
group by TCPRL.vend_co_id, TCX.LEGAL_NM
) 
GO

CREATE FUNCTION dbo.udf_functest(@ID int)
RETURNS @ReturnTable TABLE (TranID int, Amount money)
AS 
BEGIN
       DECLARE @tempTable TABLE (ID int, TranID int, Amount money)
       INSERT INTO @tempTable
       SELECT ID,
              TransactionID,        
              Amount
       FROM dbo.Table1
       WHERE ID = @ID

       INSERT INTO @ReturnTable
       SELECT TranID,
              Amount
       FROM dbo.Table2 t1
              INNER JOIN @tempTable t2 ON t1.ID = t2.ID       
       RETURN
END
GO

We walk in the dark places no others will enter
We stand on the bridge and no one may pass