Introduction

When I started using SQL Server 2005, I found myself needing the ability to pass a table into a SQL Function. Casting my net about the Net, I found the guru of SQL arrays to be SQL MVP Erland Sommarskag. The first article of his that I found (Arrays and Lists in SQL Server 2005), which has since been updated, gave me plenty of ideas that I ended up putting into practice.

In fact he has now gone well beyond what I found in my initial investigation, including performance testing and passing Table-valued Parameters, with the series of articles that can be found here. Even if you don’t read the rest of this article, I suggest you bookmark that last link now!

My early experimentation included all of the following methods to pass an array into a stored PROCEDURE (all but the last work for FUNCTIONs as well):

Delimited lists using 2 delimiters: one for rows and one for columns (there is also an article by SQL MVP Jeff Moden on this topic: Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays)
Packing the table into an XML document and passing that as the parameter
Passing what I think of as a “handle” to an XML document (Mr. Sommarskog refers to it as the OPENXML method)

Suffice it to say that while they worked, I found each of these methods somewhat wanting.

This article is primarily about the SQL TABLE TYPE (introduced in SQL 2008), and how to define and use them, so we cannot help but mention using them as Table-valued Parameters (TVPs). But what is also interesting is how they can be used beyond this very effective method of passing arrays into FUNCTIONs and stored PROCEDUREs.

CREATE the TABLE Type

First, we’ll need to create a simple table TYPE we can use in our examples.

-- Example of creating a TABLE TYPE
CREATE TYPE dbo.MyTable AS TABLE
(
    ID      INT IDENTITY PRIMARY KEY
    ,Value  INT
);

We’ve included only two columns in this simple example. To use the table type in a SQL script, we can do something like this.

DECLARE @T dbo.MyTable;
INSERT INTO @T VALUES(6),(4),(10),(11);
SELECT * FROM @T;
-- Results:
ID   Value
1    6
2    4
3    10
4    11

The declared temporary table @T acts in all respects like a table that was declared defining all of the columns. Not surprisingly, to DROP the object we created, the command is:

DROP TYPE dbo.MyTable;

Passing a TABLE into a FUNCTION or Stored PROCEDURE (Table-valued Parameters)

Let’s now create a simple Stored Procedure where we pass in the table type as a parameter.

CREATE PROCEDURE dbo.Example1
(
    @TableInSP  dbo.MyTable READONLY
)
AS BEGIN
    SELECT *
    FROM @TableInSP
    WHERE Value >= 10
END

The READONLY property must be specified in the description of the Table-valued Parameter (TVP) in the SPs header. We can then execute the SP, pass in the TVP and see the results:

DECLARE @T dbo.MyTable;
INSERT INTO @T VALUES(6),(4),(10),(11);
EXEC dbo.Example1 @TableInSP = @T;
-- Results:
ID Value
3 10
4 11

Our SP has made use of the table of data we have passed in through the TVP. Interestingly, most parameters to a SP can be given a default value to the right of the declaration in the SP’s header. When you do that, the defaulted SP parameter is optional. Here’s an example of how something like that would look.

ALTER PROCEDURE dbo.Example1
(
    @TableInSP  dbo.MyTable READONLY
    ,@Example   INT = 10
)
AS BEGIN
    SELECT *
    FROM @TableInSP
    WHERE Value >= @Example
END

A couple of example calls to the SP demonstrate how it works.

DECLARE @T dbo.MyTable;
INSERT INTO @T VALUES(6),(4),(10),(11);
EXEC dbo.Example1 @TableInSP = @T;
EXEC dbo.Example1 @TableInSP = @T, @Example = 6;
-- Results (first EXEC):
ID Value
3 10
4 11
-- Results (second EXEC):
ID Value
1 6
3 10
4 11

You cannot however, specify a default for a TVP. Interestingly though, a TVP defined in the header of an SP is always optional. For example, you can try this EXEC of the SP:

EXEC dbo.Example1;

You will find, perhaps surprisingly, that no errors are generated and the result set returns the columns of our defined TABLE TYPE (MyTable) with no rows!

A TABLE TYPE as a Pattern

In an earlier article (Logging and Error Handling for SQL Stored Procedures), I suggested using a TABLE TYPE as a pattern to reduce the amount of code present in a SP to define a temporary table. The example for this is quite similar to the prior SP except that we no longer pass in the TVP. Since this can also be done in a Table-valued FUNCTION (TVF), we’ll show that instead.

CREATE FUNCTION dbo.Example2
(
    @Multiplier INT
)
RETURNS @Results TABLE (ID INT, Value INT, Multiplied INT)
WITH SCHEMABINDING              -- optional
AS BEGIN
    DECLARE @T dbo.MyTable;
    INSERT INTO @T (Value) VALUES(6),(4),(10),(11);
    INSERT INTO @Results
    SELECT ID, Value, @Multiplier * Value
    FROM @T;
    RETURN
END

Calling this TVF returns the following results:

SELECT * FROM dbo.Example2(15);
-- Results:
ID   Value   Multiplied
1    6       90
2    4       60
3    10      150
4    11      165

In a case where MyTable represents a TABLE TYPE with scores of columns, you can see how that would significantly reduce the amount of code within the FUNCTION (or SP). But you probably should take care to follow the best practice of explicitly naming the columns you are INSERTing into any temporary tables based on the TABLE TYPE, as I have done in my Example2 FUNCTION, to ensure that when your successor comes along and modifies the TABLE TYPE you won’t get any nasty surprises.

Dynamic SQL

Normally when you’re using Dynamic SQL, if you need to reference a table that you intend to discard, you must use a temporary table instead of a table variable. Any table variable you define outside of the dynamic SQL string will be outside of the execution scope of the Dynamic SQL. However, with a TABLE TYPE, it is possible to reference a table variable within the Dynamic SQL that was created outside of the Dynamic SQL.

Here’s an example:

DECLARE @T dbo.MyTable;
INSERT INTO @T
VALUES(6),(4),(10),(11);
EXEC sp_executesql N'SELECT * FROM @DynSQLTable;'
    ,N'@DynSQLTable  dbo.MyTable READONLY'
    ,@DynSQLTable = @T;
-- Results:
ID Value
1 6
2 4
3 10
4 11

Note how we’ve included the READONLY property on the parameter definition, the same as we did when we passed the TVP into a SP.

Finally, passing the TVP into the Dynamic SQL is optional, so this will also execute without error:

DECLARE @T dbo.MyTable;
INSERT INTO @T
VALUES(6),(4),(10),(11);
EXEC sp_executesql N'SELECT * FROM @DynSQLTable;'
    ,N'@DynSQLTable  dbo.MyTable READONLY';
-- Results:
ID Value

The result returns zero rows just like it did in the similar case for the SP.

Locating TABLE TYPES in your Database

Once you get to thinking about all of the ways you can use a TABLE TYPE, you may find yourself creating many of them. In those cases, you most definitely need to keep track of them.

User-defined Table Types can be exposed in the SQL Server Management Studio’s Object Explorer following the path shown below.

Also, a quick way to expose most of the important properties of a TABLE TYPE is to use the script that I provided in the article I referenced earlier: Logging and Error Handling for SQL Stored Procedures. You can run that script now as:

SELECT TableTypeName=CASE rn WHEN 1 THEN TableTypeName ELSE '' END
    ,ColumnName, ColumnType, max_length
    ,[precision], scale, collation_name
    ,[Nulls Allowed]=CASE is_nullable WHEN 1 THEN 'YES' ELSE 'NO' END
    ,[Is Identity]=CASE is_identity WHEN 1 THEN 'YES' ELSE 'NO' END
    ,[Is In Primary Key]=CASE WHEN index_column_id IS NULL THEN 'NO' ELSE 'YES' END
    ,[Primary Key Constraint Name]=CASE rn WHEN 1 THEN ISNULL(PKName, '') ELSE '' END
FROM
(
    SELECT TableTypeName=a.name, ColumnName=b.name, ColumnType=UPPER(c.name)
        ,b.max_length, b.[precision], b.scale
        ,collation_name=COALESCE(b.collation_name, a.collation_name, '')
        ,rn=ROW_NUMBER() OVER (PARTITION BY a.name ORDER BY b.column_id)
        ,b.column_id
        ,b.is_nullable
        ,b.is_identity
        ,e.index_column_id
        ,PKName = d.name
    FROM sys.table_types a
    JOIN sys.columns b ON b.[object_id] = a.type_table_object_id
    JOIN sys.types c
        ON c.system_type_id = b.system_type_id
    LEFT JOIN sys.key_constraints d ON b.[object_id] = d.parent_object_id
    LEFT JOIN sys.index_columns e
        ON b.[object_id] = e.[object_id] AND e.index_column_id = b.column_id
    WHERE c.system_type_id = c.user_type_id
) a
ORDER BY a.TableTypeName, column_id;

And see these results (all of your TABLE TYPEs will be listed):

Conclusion

In this article we have learned how to CREATE/DROP a TABLE TYPE and we have presented some of the various ways that it can be used. We’ve shown that it can be passed into SPs as a Table-valued Parameter, and while we didn’t show it the same thing can be done for a FUNCTION.

For SPs, passing in the TVP is optional on the EXEC statement. In that case, the table can still be referenced in the SP but will always contain zero rows.

TABLE TYPEs can also be used as a pattern (or template) for a table that consists of many columns, simplifying cases where you may need to DECLARE the same temporary table in many SPs or FUNCTIONs.

We have also shown you how you can pass a TVP into Dynamic SQL using a TABLE TYPE, and expose all of the TABLE TYPEs you have defined in your database.

Below are some DROPs you can use to clean up your sandbox if you’ve been following along.

DROP FUNCTION dbo.Example2;
DROP PROCEDURE dbo.Example1;
DROP TYPE dbo.MyTable;

Thanks for listening to my ruminations today folks!

Dwain Camps

SQL Enthusiast

Follow @DwainCSQL

The SQL TABLE Type: Beyond Table-Valued Parameters