Query With Join to Table Valued Function

Posted: September 3, 2010 in SQL Server 2005

Sometimes you may need to join an existing query with the results from a table valued function. To do this you can try the following simple example and see how it works:

-- Create a simple table valued function
CREATE FUNCTION dbo.dbf_table_valued_fxn() RETURNS @tbl_value TABLE (seq_id NUMERIC(18,0))
AS 
BEGIN
   -- Insert some values into the table that is returned
   INSERT INTO @tbl_value VALUES (4);
   INSERT INTO @tbl_value VALUES (5);
   RETURN;
END
GO

-- Check that the table valued function returns the correct values
SELECT *
FROM   dbo.dbf_table_valued_fxn()
GO

-- Create table to hold list of sequences
CREATE TABLE dbo.my_sequence
            (seq_id    NUMERIC(18,0) IDENTITY,
             seq_descr VARCHAR(200))

-- Insert some values into the table
INSERT INTO dbo.my_sequence (seq_descr) VALUES ('Sequence 1')
INSERT INTO dbo.my_sequence (seq_descr) VALUES ('Sequence 2')
INSERT INTO dbo.my_sequence (seq_descr) VALUES ('Sequence 3')
INSERT INTO dbo.my_sequence (seq_descr) VALUES ('Sequence 4')
INSERT INTO dbo.my_sequence (seq_descr) VALUES ('Sequence 5')
GO

-- Check that the table has the correct values
SELECT *
FROM   dbo.my_sequence
GO

-- Select the results from the table based on the table valued function
SELECT ms.*
FROM   dbo.my_sequence                       ms
       INNER JOIN dbo.dbf_table_valued_fxn() tvf ON tvf.seq_id = ms.seq_id
GO


-- Drop table
DROP TABLE dbo.my_sequence
GO

-- Drop table valued function
DROP FUNCTION dbo.dbf_table_valued_fxn
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s