Table-valued Parameters

Last post I said I was going to try to get better at posting daily. I must apologize, as I realize it has been over a month, despite the best of intentions. Things are going well and work is getting busy, life is getting busier as well. My wife and I are dealing with our first teenager. I'm sure there are those of you who can sympathize, and those that can't yet, run, while you can. Luckily for me, our oldest son is a good kid, so things could be much, much worse.

Thanks to a gentleman I spoke with yesterday. I was interviewing him for a position at Sogeti, and he actually saw my blog link in my email signature and took the time to peek at it. His interest and feed back have once again prodded me into action. If you read my articles and like them but find I'm not keeping pace with a daily blog, please feel free to drop me an email at sql.slinger@gmail.com. I'm always open to suggestions for topics too.

Well, enough said about that, let's get to today's post.

SQL Server 2005 gave us Table data types for use as variables and return values from functions. One missing piece was the ability to pass table data into a function or stored procedure as a parameter. This is now rectified in SQL Server 2008.

Table-valued parameters offer flexibility, better performance than other methods of passing a list of parameters, and reduce server round trips. They can be used in place of BULK INSERT operations and are backed up by temporary tables, which are stored in the tempdb.

ADO.NET, OLEDB and ODBC. as well as other client data access libraries, now include support for table-valued parameters

A new data type was introduced to support table-valued parameters; the user-defined table type. Not to be confused with the table data type introduced in SQL Server 2005. The user-defined table type defines the structure of the table and allows constraints, such as PRIMARY KEY, UNIQUE, and CHECK to be defined on the table type. DEFAULT constraints are not support, nor are direct index creation.

Let's look at it in action:

USE master;
GO
IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=N'DBExample')
    CREATE DATABASE DBExample;
GO
USE DBExample;
GO

--Working table definition
CREATE TABLE RequestedTopics(TopicID INT, TopicSubject VARCHAR(128));

--Here is where we create the USER-DEFINED TYPE
CREATE TYPE BlogTopicType AS TABLE(TopicID INT, TopicSubject VARCHAR(128));
GO

--We need a stored procedure to take the parameter
CREATE PROCEDURE usp_InsertBlogTopic(
    @TopicTable BlogTopicType READONLY
)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO RequestedTopics(TopicID, TopicSubject)
    SELECT TopicID, TopicSubject FROM @TopicTable;
END
GO

--Now let's reference the table type we created
DECLARE @BTT AS BlogTopicType;

--Populate it with data
INSERT INTO @BTT(TopicID, TopicSubject) VALUES(1,'Transparent Encryption');
INSERT INTO @BTT(TopicID, TopicSubject) VALUES(2,'Service Broker Enhancements');
--Now we may as well use something we learned before
INSERT INTO @BTT(TopicID, TopicSubject) 
    VALUES(3,'Analysis Services'),(4,'Reporting Services'),(5,'Integration Services');

--Run the stored proc with the table-valued parameter
EXECUTE usp_InsertBlogTopic @BTT;

--Now let's see what's in our work table
SELECT * FROM RequestedTopics;

Output:
------- ---------------
TopicID TopicSubject
1        Transparent Encryption
2        Service Broker Enhancements
3        Analysis Services
4        Reporting Services
5        Integration Services