Joining to a Table Function in SQL Server
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