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: