SQL Server 2008 TSQL - Table Valued Parameters
Table Valued Parameters have to be one of the
coolest features in SQL Server 2008. With them you are able to pass a set of
data around as a single variable. What this means is that if you are saving
multiple records you can do very easily in one procedure call.
You might say well I can do that already using an XML column or a delimited
list, Well the difference with a Table Valued Parameter is that you get a typed
rowset which you can use directly in an INSERT, SELECT, UPDATE statement, and
anyother statement that can take a rowset. This means you don't have to shred
your XML or have a function to split your delimited string.
create table myTable (col1
int, col2
datetime, col3
varchar(max))
go
create type myTableType as
table (col1
int, col2
datetime, col3
varchar(max))
go
create procedure up_myTable_insertFromTable
@myTableData myTableType readonly
as
insert
into myTable (col1, col2, col3 )
select
col1, col2,
col3
from @myTableData
go
declare @myTable
myTableType
insert into @myTable values(1, getdate(), 'Table Valued Parameters
are great')
exec
up_myTable_insertFromTable @myTable
Its very easy to use.
There are a few gotchas to be aware of,
The stored procedure parameter has to be READONLY. This
means that you cannot modify it within the stored procedure. You can only modify
it in the scope it was created. i.e. If you created a table type variable in
your procedure you can modify the data in it, however if you pass that to
another procedure that other procedure cannot modify it as it wasn't create in
the scope of that procedure.
A table valued parameter ALWAYS has a default of an empty
table. This is not inline with all other types btu has been done for
performance reasons. What this means is that in the previous code the following
will work
exec
up_myTable_insertFromTable
Even though you haven't passed in a value
for the @myTableData parameter.
No statistics are created on a TVP.
This is inline with table variables which means if you are doing
selects with where clauses you have to be careful, as you may end up with table
scans.
-