Systems Engineering and RDBMS

Table Valued Parameters in SQL Server 2008

Posted by decipherinfosys on May 10, 2009

We had blogged about table valued parameters (TVP) before when SQL Server 2008 was in it’s early CTP stages.  Got a chance to use it in one of our projects recently.  As you can read from the previous post, the table valued parameters are new type of parameter which allows use to use an array of data in T-SQL as well as enables us to send this entire table data set as a parameter to a stored procedure or a function.  The client libraries (ADO.Net, OLEDB and ODBC) also have support for table valued parameters.  So, what exactly is it and how can we go about using it?

Table valued parameters are supported through a new data type in SQL Server 2008 – the user defined table type.  This type defines the structure for a table and constraints like primary key, unique and check constraints are allowed but creation of indexes are not supported for these types (yet).

Let’s create physical tables first:

CREATE TABLE dbo.RCD_HDR (COL1 INT PRIMARY KEY, COL2 NVARCHAR(10));
CREATE TABLE dbo.RCD_DTL (COL3 INT, COL4 INT, COL5 NVARCHAR(10), CONSTRAINT PK_RCD_DTL PRIMARY KEY (COL4, COL3), CONSTRAINT FK_RCD_DTL_TO_HDR FOREIGN KEY (COL4) REFERENCES dbo.RCD_HDR(COL1));

So, we have a header and a detail relationship now.  Let’s create a table type now and then we will see how we can pass that along to a stored procedure to demonstrate passing of records to do the inserts into these tables.  Since we are new to this, let’s pull out the table type template first – so, look at the image below and use that template:

TYPE_1Now, let’s replace the values in the type template to create our data set:

/* Create the TVP*/
CREATE TYPE dbo.RCD_DATA_TYPE AS TABLE
(
COL1    INT
,    COL2    NVARCHAR(10)
,    COL3    INT
,    COL5    NVARCHAR(10)
PRIMARY KEY (COL1, COL3)
)
GO

And here we have the entire data-set for the header and detail combined.  Let’s now create a variable of this type and dump in some records into it:

declare @rcd_data as RCD_DATA_TYPE

Insert into @rcd_data
values        (1, ‘1st Record’, 1, ‘Dtl Rcd-1’)
,    (1, ‘1st Record’, 2, ‘Dtl Rcd-2’)
,    (1, ‘1st Record’, 3, ‘Dtl Rcd-3’)
,    (2, ‘2nd Record’, 1, ‘Dtl Rcd-1’)

select * from @rcd_data

COL1        COL2       COL3        COL5
———– ———- ———– ———-
1           1st Record 1           Dtl Rcd-1
1           1st Record 2           Dtl Rcd-2
1           1st Record 3           Dtl Rcd-3
2           2nd Record 1           Dtl Rcd-1

Next thing to do now is to create a simple stored procedure to demonstrate this functionality and pass this data set into that stored procedure:

create proc dbo.usp_rcd_insert
(
@rcd RCD_DATA_TYPE READONLY
)
as
begin
set nocount on
insert into dbo.RCD_HDR (COL1, COL2)
select distinct col1, col2
from @rcd

insert into dbo.RCD_DTL (COL3, COL4, COL5)
select col3, col1, col5
from @rcd

end
go

The parameter is passed into the stored procedure as a read only parameter i.e. no changes are allowed to the table type parameter within the body of the stored procedure.  If you try to do that, you will get an error at creation time itself:

Msg 10700, Level 16, State 1, Procedure usp_rcd_insert, Line 9
The table-valued parameter “@rcd” is READONLY and cannot be modified.

Now, let’s build the T-SQL block to pass in the values and execute the proc:declare @rcd_data as RCD_DATA_TYPE – we will use the row value constructor feature of SQL Server 2008 to create the data using a single insert statement:

Insert into @rcd_data
values        (1, ‘1st Record’, 1, ‘Dtl Rcd-1’)
,    (1, ‘1st Record’, 2, ‘Dtl Rcd-2’)
,    (1, ‘1st Record’, 3, ‘Dtl Rcd-3’)
,    (2, ‘2nd Record’, 1, ‘Dtl Rcd-1’)

exec dbo.usp_rcd_insert @rcd_data

And we can check the records in the tables now:

SELECT * FROM RCD_HDR

COL1        COL2
———– ———-
1           1st Record
2           2nd Record

SELECT * FROM RCD_DTL

COL3        COL4        COL5
———– ———– ———-
1           1           Dtl Rcd-1
2           1           Dtl Rcd-2
3           1           Dtl Rcd-3
1           2           Dtl Rcd-1

And regarding permissions, one has to assign either the control or the execute permission to the user account in order for that account to be able to use this user defined table type.  The syntax can be seen from BOL here.

So, as you can see from above, this is a very good addition to SQL Server.  Oracle has had this functionality since a very long time and the advantages are very obvious ones:

– bulk operations can be done using a TVP,
– we can pass in arrays into stored procedures or functions for processing of the data,
– we can query upon that data whichever way we want and can do calculations based on that data just like we would on any other table.

The current disadvantages (rather limitations) are:

– it is a read only parameter so we cannot have DML operations upon that table type parameter,
– In addition to the select statement, TVP can also be used in FROM clause of the SELECT INTO as well as INSERT..EXEC commands but cannot be used as the target for those commands i.e. you cannot insert data into TVP using those commands.
– Creation of indexes is not allowed on them.

References:

  • Post on sqlteam.com by Kathi Kellenberger – here.
  • Screencast @ MSDN by Mike Taulty – here.
  • BOL enteries – for the T-SQL language – here and for ADO.Net – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: