Using Table Variable in Place of Temporary Table in UDF(User Defined Functions).

For SQL Programs there cases where Complex Queries Needs to be Executed for each row of Select Statement, But SQL Have Few Limitations for Security Purpose as Listed Below Which makes Programming much Complex Otherwise would have been much easier.

Limitaions of T-SQL :

1. Select Statement Cannot Directly Execute Stored Procedure.

2. User Defined Functions Cannot Execute Stored Procedures Other than Extended Stored Procedures.

3. User Defined Functions Cannot Use Temporary Table.

Although These Restriction of SQL Server is to Check against sql injection with in Stored Procedures and Functions hence making SPs and Function Safer for SQL injection but also adds Much over-head for Programmers.

Now to Accomplish These Functionality we need to Use Alternative in All the Cases.

Table Variable which have Scope only until the scope of the Transaction, can be a First Choice as an alternative in above cases.

Example Below Shows How we Can Return a Scalar Value after Executing a Complex Calculation with in Table Variable.

CREATE FUNCTION [dbo].[GetBalLoan]
(
    @LoanIssueID INT
)
RETURNS DECIMAL(14,4)
AS
BEGIN

DECLARE @InterestRate INT = (SELECT InterestRate FROM ln_loanissue
                                JOIN ln_loanType ON ln_loanissue.LoanTypeID = ln_loanType.loantypeid
                            WHERE Loanissueid = @LoanIssueId)
DECLARE @i INT = 1, @result DECIMAL(14, 4) = 0
DECLARE @temp3 TABLE (
    ID SMALLINT PRIMARY KEY IDENTITY(1, 1),
    LoanIssueID INT ,
    EffectiveFrom  DATE,
    Dr_Loan DECIMAL(14, 4),
    Cr_Loan DECIMAL(14, 4),
    Dr_Int Decimal(14, 4),
    Cr_Int Decimal(14, 4),
    Cal_Int Decimal(14, 4),
    Bal_Loan Decimal(14, 4),
    Bal_Int Decimal(14, 4),
    Adjustment Decimal(14, 4)
)

insert into @temp3(LoanIssueID,EffectiveFrom,Dr_Loan,Cr_Loan,Dr_Int,Cr_Int,Cal_Int,Bal_Loan,Bal_Int, Adjustment)

select LoanIssueID,EffectiveFrom,Amount as Dr_Loan,null as Cr_Loan,null as Dr_Int,null as Cr_Int,Null as Cal_Int,
null as Bal_Loan ,null as Bal_Int, Null as  Adjustment from ln_loanissue where loanIssueid=@LoanIssueId
Union all
Select LoanIssueID,PaidDate,null,isnull(InterestAmount,0)+isnull(PrincipalAmount,0) ,null,null, Null,
NULL,NUll,Null from ln_loanPaid where loanIssueid=@LoanIssueId

order by EffectiveFrom


while(@i<=(select max(ID) from @temp3))
begin
--Here Other task on data can be performed by getting col1 for uniqueid in @temp3

update @temp3 set Cal_Int=(isnull( (case when @i=1 then 0
 else (select isnull(Bal_Loan,0) from @temp3 where ID=@i-1)*@InterestRate/100/365*DateDiff(day,(select EffectiveFrom from @temp3 where ID=@i-1),EffectiveFrom)
end),0))
where ID=@i
;

Update  @temp3 set Bal_Int=(isnull((case when @i=1 then 0
else
case when (isnull(Cal_Int,0)+(select isnull(Bal_Int,0) from @temp3 where ID=@i-1))<isnull(Cr_Loan,0) then 0 else  (isnull(Cal_Int,0)+(select isnull(Bal_Int,0) from @temp3 where ID=@i-1))-isnull(Cr_Loan,0) end
end),0))
where ID=@i
;

update @temp3 set Bal_loan = (isnull(( case when @i=1 then (isnull(Dr_Loan,0)-isnull(Cr_loan,0))
else
case when isnull( Bal_Int,0)>0 then (select isnull( Bal_Loan,0) from @temp3 where ID=@i-1) else
(select isnull( Bal_Loan,0) from @temp3 where ID=@i-1)-isnull(Cr_loan,0)+isnull(Cal_Int,0) end
end),0))
where ID=@i
;

set @i=@i+1;
end

select @result= Bal_loan from @temp3 group by ID,Bal_loan having ID=(select max(ID) from @temp3)
return @result --as Bal_Loan-- @LoanIssueId as LoanIssueID,

return @result

END



Now We can call the UDF in select statement.

select [dbo].[GetBalLoan](16) ;

This Statement can be fabricated with more table and Values as per need as in another example below.

select [dbo].[GetBalLoan](Loan.IssueID) ,Loan.* from LoanIssueTable ;

Syntactic Difference Between Temporary Table Declaration and Table Variable Declaration

For Temporary Table The Syntax Is:

Syntax: CREATE TABLE #tablename(columns...);

Example:
create table #Temp(
ID smallint Primary Key IDENTITY(1,1),
LoanIssueID int ,
EffectiveFrom  Date,
Dr_Loan Decimal(14,4),
Cr_Loan Decimal(14,4),
Dr_Int Decimal(14,4),
Cr_Int Decimal(14,4),
Cal_Int Decimal(14,4),
Bal_Loan Decimal(14,4),
Bal_Int Decimal(14,4),
Adjustment Decimal(14,4)
);

For Table Variable The Syntax Is:

Syntax:  DECLARE @tablename TABLE ( columns...);

Example:
DECLARE @temp3 TABLE (
    ID SMALLINT PRIMARY KEY IDENTITY(1, 1),
    LoanIssueID INT ,
    EffectiveFrom  DATE,
    Dr_Loan DECIMAL(14, 4),
    Cr_Loan DECIMAL(14, 4),
    Dr_Int Decimal(14, 4),
    Cr_Int Decimal(14, 4),
    Cal_Int Decimal(14, 4),
    Bal_Loan Decimal(14, 4),
    Bal_Int Decimal(14, 4),
    Adjustment Decimal(14, 4)
)