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.

 



-
Published Saturday, January 19, 2008 4:28 PM by simonsabin

Comments

# Reflective Perspective - Chris Alcock » The Morning Brew #14