Joining to a Table Function in SQL Server

Posted on Friday, September 9th, 2011 at 8:20 pm in

Following up on my previous post, SQL Server (since version 2005 while in SQL 90 compatibility mode) provides the ability to join to the table created by a parameterized table function. In earlier versions you were not able to use a table function with a dynamic parameter or even join to it. If you needed this functionality your best bet was a stored procedure.

So in my case I was refactoring some code to correct for a PowerBuilder datawindow issue and needed to split up a block of text from a table into individual rows based on the carriage return / linefeed codes contained within it. Since I needed additional data from another table to properly group the data I came up with the following as a datasource for the datawindow.

SELECT	sn.Item
FROM 		dbo.Customer
LEFT JOIN	dbo.CustomerNotes ON dbo.Customer.cust_id = dbo.CustomerNotes.cust_id
CROSS APPLY dbo.uft_splitnotes(dbo.CustomerNotes.note_txt, '[CRLF]') sn 
WHERE dbo.Customer.cust_id = 1

Note the use of CROSS APPLY which functions like an INNER JOIN. To simulate a LEFT JOIN use OUTER APPLY.

The following script snippits can be run against your database to demonstrate the use of this technique. I am duplicating the table function script from my previous post for the sake of completeness.

CREATE TABLE Customer (
cust_id Int
,cust_name nvarchar(100)
)

CREATE TABLE CustomerNotes (
note_id int
,cust_id INT
,note_dt datetime
,note_txt nvarchar(max)
)

INSERT INTO Customer (
cust_id, cust_name)
SELECT 1, 'Koala Quarries'

INSERT INTO Customer (
cust_id, cust_name)
SELECT 2, 'Ironclad Aluminum'


INSERT INTO CustomerNotes (
note_id, cust_id, note_dt, note_txt)
SELECT 1, 1, Getdate(), 'I spoke with' + CHAR(13) + CHAR(10) + 'Jim to discuss the June shipment.' + CHAR(13) + CHAR(10) + 'Will follow up on Monday.'


CREATE FUNCTION [dbo].[uft_splitnotes] (@StringArray NVARCHAR(MAX),
                                        @Delimiter   NVARCHAR(30))
RETURNS @Results TABLE 
(
  SeqNo INT IDENTITY(1, 1),
  Item  NVARCHAR(MAX)
)
AS
  BEGIN
      DECLARE @Next INT
      DECLARE @lenStringArray INT
      DECLARE @lenDelimiter INT
      DECLARE @ii INT
      --initialise everything
      IF UPPER(@Delimiter) = 'SPACE'
        BEGIN
            SET @Delimiter = ' '
        END
      IF UPPER(@Delimiter) = '[CR]'
        BEGIN
            SET @Delimiter = CHAR(13)
        END
      IF UPPER(@Delimiter) = '[LF]'
        BEGIN
            SET @Delimiter = CHAR(10)
        END
      IF UPPER(@Delimiter) = '[CRLF]'
        BEGIN
            SET @Delimiter = CHAR(13) + CHAR(10)
        END
      IF UPPER(@Delimiter) = 'CHAR(13)'
        BEGIN
            SET @Delimiter = CHAR(13)
        END
      IF UPPER(@Delimiter) = 'CHAR(10)'
        BEGIN
            SET @Delimiter = CHAR(10)
        END
      IF @Delimiter = 'CHAR(13) + CHAR(10)'
        BEGIN
            SET @Delimiter = CHAR(13) + CHAR(10)
        END

      SELECT @ii = 1,
             @lenStringArray = LEN(REPLACE(@StringArray, ' ', '|')),
             @lenDelimiter = LEN(REPLACE(@Delimiter, ' ', '|'))
      --notice we have to be cautious about LEN with trailing spaces!
      --while there is more of the string…
      WHILE @ii <= @lenStringArray
        BEGIN--find the next occurrence of the delimiter in the stringarray
            SELECT @Next = CHARINDEX(@Delimiter, @StringArray + @Delimiter, @ii)

            INSERT INTO @Results
                        (Item)
            SELECT SUBSTRING(@StringArray, @ii, @Next - @ii)
            --note that we can get all the items from the list by appeending a
            --delimiter to the final string
            SELECT @ii = @Next + @lenDelimiter
        END
      RETURN
  END 
  
---- selects to show the data from above
select * from CustomerNotes

SELECT	dbo.Customer.cust_id
		, dbo.Customer.cust_name
		, dbo.CustomerNotes.note_dt
		, sn.Item
FROM 		dbo.Customer
LEFT JOIN	dbo.CustomerNotes ON dbo.Customer.cust_id = dbo.CustomerNotes.cust_id
OUTER APPLY dbo.uft_splitnotes(dbo.CustomerNotes.note_txt, '[CRLF]') sn 

SELECT	dbo.Customer.cust_id
		, dbo.Customer.cust_name
		, dbo.CustomerNotes.note_dt
		, sn.Item
FROM 		dbo.Customer
LEFT JOIN	dbo.CustomerNotes ON dbo.Customer.cust_id = dbo.CustomerNotes.cust_id
CROSS APPLY dbo.uft_splitnotes(dbo.CustomerNotes.note_txt, '[CRLF]') sn

Top