SQL Server 2008: Table-valued parameters

Table-valued types and parameters are among the many exciting new features of the recently released SQL Server 2008. These table-valued types allow you to pass table data to stored procedures and functions.

But why is this useful? In previous versions of SQL Server one could pass parameters to stored procedures and functions. A single parameter can describe a single value. But what did you do if the information you want to pass to a stored procedure is more dynamic? If not all information is required for the stored procedure? For example a customer order that has optional delivery time or priority information? You could pass null values, but if these make the code look rather unreadable. Previous solutions included optional parameters for stored procedures. Parameters that had a default value need not be specified. This made stored procedure calls nicer, but the parameter list of stored procedures could quickly become unmanageable. The situation was aggravated when new parameters were introduced.

Furthermore, this solution could not handle a different type of dynamic information, for example a customer order that contains multiple items. People started to use dirty hacks to circumvent this restriction, and used comma separated lists, and later XML. However, a database management system is certainly not ideal for processing strings or XML, especially when using T-SQL.

Another alternative was using a communication table that was either fixed or created on demand. This solution suffered too, because when the communication table was permanent, it needed regular clean up, concurrency needed to be handled manually, and with temporary tables there could be recompilation issues.

Using communication tables was also not ideal from the point of view of roundtrips or code simplicity between the applications and the database. Multiple insert statements needed to be executed just to set up the stored procedure call, all of this put into a single transaction, etc.

Well, this is hopefully the past, welcome the new table-valued types and parameters. So let’s see an example.

In order to use table-valued parameters one needs to set up a table type. This looks like the crossbreed of a user defined type and a table definition:

CREATE TYPE myTableType AS TABLE
    (
      id INT NOT NULL
             PRIMARY KEY,
      data NVARCHAR(100)
    )

Note that you can specify a primary key, constraints, computed columns, etc. This gives you a lot of flexibility, and this extra control will help you to optimize performance and ensure data integrity.

Once you have set up a table type, you can use it as a stored procedure or function parameter. In the following example we just select the information inside the table that is passed in as a parameter:

CREATE PROCEDURE myProcedure
    ( @TableVariable myTableType READONLY )
AS
    BEGIN
        SELECT  *
        FROM    @TableVariable
    END
GO

As the above example shows, you can query the table parameter as if it were an ordinary table variable. However, you cannot modify the table parameter, and this is spelled out explicitly by the mandatory “READONLY” word after the parameter specification.

To see how the above works in T-SQL we can declare a table variable using our new table type, insert data into this table variable (and I also use the new row constructor in SQL Server 2008 to insert multiple rows in a single statement), and call the stored procedure with the table variable as its parameter:

DECLARE @table myTableType
INSERT  INTO @table
VALUES  ( 1, ‘a’ ),
        ( 2, ‘b’ ),
        ( 3, ‘c’ )
EXEC dbo.myProcedure @table

After executing the above we get a resultset like:

id          data
———– —————-
1           a
2           b
3           c

(3 row(s) affected)

The above may simplify the work of people writing stored procedures and functions, but what about developers. Well, the above works just as well with ADO.Net. When creating a SQLCommand object for a stored procedure, one can now pass in a DataTable object as a parameter, exactly as it is done in the following C# code segment:

SqlConnection connection = new SqlConnection(“server = …”);
 
SqlCommand command = connection.CreateCommand();
command.CommandText = “dbo.myProcedure”;
command.CommandType = CommandType.StoredProcedure;
 
// declare a table to store the parameter values
DataTable paramTable = new DataTable();
paramTable.Columns.Add(“id”, typeof(int));
paramTable.Columns.Add(“data”, typeof(string));
 
// add the table as a parameter to the stored procedure
SqlParameter sqlParam = command.Parameters.AddWithValue(“@TableVariable”, paramTable);
sqlParam.SqlDbType = SqlDbType.Structured;
sqlParam.TypeName = “dbo.myTableType”;
 
// add rows to the table parameter
paramTable.Rows.Add(new object[] {1, “a”});
paramTable.Rows.Add(new object[] {2, “b” });

 Note that there are no insert statements; we can just use a DataTable to pass in table parameters to the stored procedure. Hopefully this solution in SQL Server 2008 will get rid of all the now unnecessary “hacks” that used XML, comma separated lists and temporary tables used in a large number of database applications.

 If you want to hear more about what is new in SQL Server 2008, and you are in the UK or Poland on the following dates, do come to my presentations or talk to me later. For the first half of September my schedule looks like the following: