Results 1 to 6 of 6

Thread: SQL Server 2005 - Cross Apply with Table Value Functions

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Lightbulb 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
    Last edited by szlamany; Jan 2nd, 2008 at 04:16 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    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

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    New Member
    Join Date
    Jan 2008
    Posts
    1

    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

  6. #6

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width