Systems Engineering and RDBMS

SQL Server 2008 – Table Valued Parameters

Posted by decipherinfosys on September 7, 2007

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 TYPE TestType AS TABLE
(
EMP_NAME VARCHAR(30) NULL,
HIRE_DATE DATETIME NULL,
MGR_NO INT NULL
)
GO

And then, I can simply pass this into my procedure:

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.

DECLARE @TestTbl AS TestType

Now, I can insert the data into this table variable:

insert into @TestTbl select emp_name, hire_date, mgr_no from emp_master where emp_id < 1000

And once I am done, I can then pass this into the stored procedure:

exec my_test_proc @tbl = @TestTbl;

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.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: