SQL Server 2005 - Cross Apply with Table Value Functions

[szlamany]

This is a truly nice feature.

Look at this query

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:

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:

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).

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

[Hack]

This is something that shouldn't get lost in a pile of past threads.

Moved to the CodeBank

[techgnome]

Schnap Batman! Instantly the possibilities .... now, if we can just get our app up to SQL2005....

-tg

[szlamany]

@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.

[sqlchef]

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

[szlamany]

@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.