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
