SQL Server 2008 – Table Valued Parameters

We have blogged on the upcoming features in SQL Server 2008 (Code Named: Katmai) in some of our previous posts. You can search for “SQL Server 2008” on the blog site to see other posts. We will keep on posting new articles as and when we play with those new features (likewise for Oracle 11g as well). In this post, we will talk about another good T-SQL feature in SQL Server 2008 : The table valued parameters. In order to pass an array into a stored procedure (see previous post – here), one has to use XML in SQL Server 2005 and then de-construct it using XQUERY features or OPENXML. This, though functional is not as simple. SQL Server 2008 now introduces table valued parameters which can be passed into the stored procedures. One does not need to de-construct and parse through the XML anymore. One can just pass a table full of data i.e. an array into a stored procedure and the code can simply use it for their processing logic. Let’s see this feature with an example to make it a bit more clear:

create proc my_test_proc
(
@tbl TESTTYPE READONLY
)
as
begin
….
–your logic will go over here.
–you can use the TVP (Table Valued Parameter)
–as a regular table and do the READ-ONLY operations on it

end
go

In order to use this stored procedure now, I can declare a variable of that type, populate it with the data and then pass it in. Example:

All these types are visible under the sys.table_types system catalog.

Please remember that this data set is READONLY and hence the readonly clause is mentioned in the procedure as well. In addition, these table variables cannot be used as OUTPUT parameters – they can only be used as input parameters.

When table variables are passed in as parameters, the table resides in tempdb and not in memory…this means that we can use larger result sets also with relative ease. We will benchmark this in our test environments and post the results on it in a later blog post.

This type functionality has existed in Oracle for quite some time now so the Oracle professionals who work in SQL Server also will find this to be a welcome addition.

Share this:

Like this:

LikeLoading...

Related

This entry was posted on September 7, 2007 at 12:09 pm and is filed under SQL Server.
You can follow any responses to this entry through the RSS 2.0 feed.
Responses are currently closed, but you can trackback from your own site.