This is part of the series of posts that I am running under the title “Under Appreciated Features of SQL Server“. The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

Table-valued parameters are a new parameter type in SQL Server 2008. Because a lot of production systems still run Microsoft SQL Server 2005, I am not too surprised to find this on the underappreciated features list.

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Creating user-defined table types

In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure. Therefore, the creation of user defined table types is similar to creating a table. Let’s create a table data type for handling UNIQUEIDENTIFIER values:

USE AdventureWorks2008
GO
-- Create a GUIDTableType to hold a table of GUIDs/UNIQUEIDENTIFIERs
CREATE TYPE GUIDTableType AS TABLE 
    ( GUIDValue UNIQUEIDENTIFIER )
GO

Once created, you can use the following DMV to get a list of available table data types:

USE AdventureWorks2008
GO
SELECT * FROM sys.table_types

OR use the SSMS Object Explorer

Create routines consuming the user-defined table type

Let us create simple programming routines that consume the user-defined table type created above:

USE AdventureWorks2008
GO
IF EXISTS (SELECT * FROM sys.objects where name = 'proc_GetSalesOrdersbyRowGuid' AND type = 'P')
    DROP PROCEDURE proc_GetSalesOrdersbyRowGuid
GO
CREATE PROCEDURE proc_GetSalesOrdersbyRowGuid
    @SalesOrderRowGuids GUIDTableType READONLY
AS
BEGIN
    SELECT Sales.SalesOrderDetail.*
    FROM @SalesOrderRowGuids SalesOrderRowGuids
    INNER JOIN Sales.SalesOrderDetail ON Sales.SalesOrderDetail.rowguid = SalesOrderRowGuids.GUIDValue
END
GO

USE AdventureWorks2008
GO
IF OBJECT_ID(N'dbo.func_SalesOrderDetailInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.func_SalesOrderDetailInformation;
GO
CREATE FUNCTION dbo.func_SalesOrderDetailInformation
    (@SalesOrderRowGuids dbo.GUIDTableType READONLY)
RETURNS @retSalesOrderDetailInformation TABLE 
(
    -- Columns returned by the function
    [SalesOrderID]          [INT]          NOT NULL, 
    [SalesOrderDetailID]    [INT]          NOT NULL,
    [CarrierTrackingNumber] [NVARCHAR](25) NULL,
    [OrderQty]              [SMALLINT]     NOT NULL,
    [ProductID]             [INT]          NOT NULL,
    [SpecialOfferID]        [INT]          NOT NULL,
    [UnitPrice]             [MONEY]        NOT NULL,
    [UnitPriceDiscount]     [MONEY]        NOT NULL,
    [LineTotal]             [NUMERIC]      NOT NULL,
    [ModifiedDate]          [DATETIME]     NOT NULL
)
AS 
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
    INSERT @retSalesOrderDetailInformation
    SELECT Sales.SalesOrderDetail.SalesOrderID,
           Sales.SalesOrderDetail.SalesOrderDetailID,
           Sales.SalesOrderDetail.CarrierTrackingNumber,
           Sales.SalesOrderDetail.OrderQty,
           Sales.SalesOrderDetail.ProductID,
           Sales.SalesOrderDetail.SpecialOfferID,
           Sales.SalesOrderDetail.UnitPrice,
           Sales.SalesOrderDetail.UnitPriceDiscount,
           Sales.SalesOrderDetail.LineTotal,
           Sales.SalesOrderDetail.ModifiedDate
    FROM @SalesOrderRowGuids SalesOrderRowGuids
    INNER JOIN Sales.SalesOrderDetail ON Sales.SalesOrderDetail.rowguid = SalesOrderRowGuids.GUIDValue
    
    RETURN
END
GO

Consuming the user-defined table type

Remember how we said that user-defined table types are similar to tables? That’s true! Here’s how to consume the newly user defined table type:

USE AdventureWorks2008
GO

DECLARE @SalesOrders GUIDTableType

INSERT INTO @SalesOrders VALUES ('B207C96D-D9E6-402B-8470-2CC176C42283'),
                                ('04C4DE91-5815-45D6-8670-F462719FBCE3'),
                                ('80667840-F962-4EE3-96E0-AECA108E0D4F'),
                                ('E9D54907-E7B7-4969-80D9-76BA69F8A836'),
                                ('AC769034-3C2F-495C-A5A7-3B71CDB25D4E')

EXEC proc_GetSalesOrdersbyRowGuid @SalesOrders

SELECT * FROM func_SalesOrderDetailInformation(@SalesOrders)

Here’s what you get for output from the stored procedure:

Here’s what you get for output from the function:

Please note that table data types cannot be targets of SELECT INTO and INSERT EXEC statements. It can however, be in the FROM clause of either of these. Also, just as is the case with other parameters, a table data type is scoped to the stored procedure, function or dynamic Transact-SQL text as applicable.

For application programmers:

Per Books On Line, “Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.” So, there you have it – pointers and by-reference passing are still alive and kicking!

Benefits of the table valued table types

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. There are tons of benefits, and there can be nothing better than the list provided by MSDN:

• Do not acquire locks for the initial population of data from a client
• Provide a simple programming model
• Enable you to include complex business logic in a single routine
• Reduce round trips to the server
• Can have a table structure of different cardinality
• Are strongly typed
• Enable the client to specify sort order and unique keys

Do you use user defined table data types? If yes, how do you find this feature? Do let us know!

Until we meet next time,

Be courteous. Drive responsibly.