SQL Server 2005 - Cross Apply with Table Value Functions
This is a truly nice feature.
Look at this query
Code:
select top 50 gb.*,rt.billnum
From reltax_t rt
Left Join BillRun_T BR on BR.Yr=RT.Yr and BR.BillRun=RT.BillRun and BR.Town=RT.Town
cross apply dbo.GetBalance_TVF(rt.yr,rt.billrun,rt.town,rt.billnum
,rt.closed,rt.period,rt.discamt,rt.baseamt,rt.pltyamt
,rt.pay1amt,rt.pay2amt,rt.pay2pen,rt.pay3amt,rt.pay3pen
,br.DiscDate,br.BaseDate,br.Pltydate
,br.Pay1Date,br.Pay2Date,br.Pay3Date
,null,null) gb
where rt.yr=2007 and gb.balance<>0
That CROSS APPLY is actually JOIN'ing to a user-defined function.
That function looks something like this:
Code:
CREATE FUNCTION dbo.GetBalance_TVF (@Yr int,@BillRun int,@Town varchar(2),@BillNum varchar(7)
, @Closed varchar(1), @Period varchar(3), @DiscAmt money, @BaseAmt money, @PltyAmt money
, @Pay1Amt money, @Pay2Amt money, @Pay2Pen money, @Pay3Amt money, @Pay3Pen money
, @DiscDate datetime, @BaseDate datetime, @PltyDate datetime
, @Pay1Date datetime, @Pay2Date datetime, @Pay3Date datetime
, @Payment money
, @BalDate datetime)
RETURNS @Balance_TVF Table
(Balance money
,DiscFlag varchar(4)
,BaseFlag varchar(4)
,PltyFlag varchar(4)
,Pay1Flag varchar(4)
,Pay2Flag varchar(4)
,Pay2PenFlag varchar(4)
,Pay3Flag varchar(4)
,Pay3PenFlag varchar(4)
,Info varchar(100)
)
As
BEGIN
Declare @DiscFlag varchar(4)
Declare @BaseFlag varchar(4)
Declare @PltyFlag varchar(4)
Declare @Pay1Flag varchar(4)
Declare @Pay2Flag varchar(4)
Declare @Pay2PenFlag varchar(4)
Declare @Pay3Flag varchar(4)
Declare @Pay3PenFlag varchar(4)
Declare @Info varchar(100)
If @BalDate is null Set @BalDate=Cast(Convert(varchar(10),GetDate(),101) as datetime)
If @Payment is null Set @Payment=IsNull((Select Sum(Payment+PenAmt+Fees) From ReltaxPay_T RTP
Where RTP.Yr=@Yr and RTP.Town=@Town and RTP.BillRun=@BillRun
and RTP.BillNum=@BillNum),0)
Declare @P_Work money
If @Payment=0 -- Made no payments
Begin
If @BalDate<=@DiscDate
Begin
Set @P_Work=@DiscAmt
Set @DiscFlag=' ** '
Set @Info='No Payments made'
Goto Done
End
Else
Begin
If @BalDate<=@BaseDate
Begin
Set @P_Work=@BaseAmt
Set @BaseFlag=' ** '
Set @Info='No Payments made'
Goto Done
End
Else
Begin
Set @P_Work=@PltyAmt
Set @PltyFlag=' ** '
Set @Info='No Payments made'
Goto Done
End
End
End
Else
Notice that it returns a TABLE - not a scalar value.
The UDF ends like this:
Code:
Done:
Insert into @Balance_TVF
Select @P_Work,IsNull(@DiscFlag,''),IsNull(@BaseFlag,''),IsNull(@PltyFlag,'')
,IsNull(@Pay1Flag,''),IsNull(@Pay2Flag,''),IsNull(@Pay2PenFlag,''),IsNull(@Pay3Flag,''),IsNull(@Pay3PenFlag,'')
,IsNull(@Info,'')
Return
End
It actually "INSERT's" into the RETURN variable.
This is like a "smart" view that can incorporate T-SQL code and logic.
It's also extremely fast.
That query up top ran against 60000 bills and it ran instantly
returning (scroll to the right to see the INFO text field).
Code:
billnum Balance DiscFlag BaseFlag PltyFlag Pay1Flag Pay2Flag Pay2PenFlag Pay3Flag Pay3PenFlag Info
------- --------------------- -------- -------- -------- -------- -------- ----------- -------- -----------
0000003 685.03 ** Paid Ins 1 & 2 - Penalty on 3
0000052 1883.26 ** No Payments made
0000053 623.08 ** Paid Ins 1 & 2 - Penalty on 3
0000089 1984.96 ** No Payments made
0000093 176.63 Paid in Penalty
0000097 1681.61 ** No Payments made
0000104 2893.27 ** No Payments made
0000111 1934.11 ** No Payments made
0000139 859.79 ** Paid Ins 1 & 2 - Penalty on 3
0000147 1870.98 ** No Payments made
0000164 256.49 Paid in Penalty
0000176 0.01 Paid installments 1, 2 and 3
0000193 191.29 Paid in Penalty
0000194 202.92 Paid in Penalty
0000260 0.06 Paid in Discount
0000273 2140.45 ** Paid Ins 1 & 2 - Penalty on 3
0000328 2293.58 ** No Payments made
0000339 677.44 ** Paid Ins 1 & 2 - Penalty on 3
0000370 1914.82 ** No Payments made
0000391 1997.24 ** No Payments made
0000406 461.75 ** Paid Ins 1 & 2 - Penalty on 3
0000409 238.96 Paid in Penalty
0000464 2016.52 ** No Payments made
0000478 -1919.95 Paid in Discount
0000528 202.29 Paid in Penalty
0000543 593.85 ** Paid Ins 1 & 2 - Penalty on 3
0000546 1876.24 ** No Payments made
0000576 2000.74 ** No Payments made
0000593 1346.68 ** ** Paid Ins 1 - Penalty on 2 and 3
0000596 1883.26 ** No Payments made
Re: SQL Server 2005 - Cross Apply with Table Value Functions
This is something that shouldn't get lost in a pile of past threads.
Moved to the CodeBank
Re: SQL Server 2005 - Cross Apply with Table Value Functions
Schnap Batman! Instantly the possibilities .... now, if we can just get our app up to SQL2005....
-tg
Re: SQL Server 2005 - Cross Apply with Table Value Functions
@tg - it was a really great discovery for us here.
The amount of effort that was needed to determine the "payment" period and balance against payment dates and what not in our tax application was getting out of hand.
Our GUI wanted to display these little "asterisks" - the users only really cared about the "balance" being accurated.
We decided that since it was a UDF that we could add the INFO field to the output so that from a debugging standpoint we would know exactly how a bill resolved itself against payments.
What a great way to put 100% of the business logic for a task into a single place that can be used virtually everywhere we need it.
Re: SQL Server 2005 - Cross Apply with Table Value Functions
Thanks for the post szlamany, it cleared up a couple of things for me regarding TVF's.
Excellent post, implemented TVF with some assistance from your code example and the process went from 7+ minutes down to 2 minutes.
Thanks again, definitely a recipe for my cookbook!
SQLChef
Re: SQL Server 2005 - Cross Apply with Table Value Functions
@sqlchef - you are very welcome.
I saw this feature when 2005 first came out - but until I really needed it and used it I didn't appreciate the power of a "view" with "user-defined" function like logic.
