Home > SQL Server > Table Valued Parameters : Making life easier

Table Valued Parameters : Making life easier

Temporary tables help in certain situations – you may need to store a subset of a tables data temporarily to transform it or use in another calculation. Many junior developers, however, are not aware of the drawbacks of using temporary tables. These include :
1) These temporary tables are created in TempDB and are prone to locking and blocking.
2) You have to remember to drop it afterwards ( unlike a table variable ). People FORGET TO DO THIS !
3) Usage of temporary tables leads to frequent stored procedure re-compilations.
The last point will impact performance and you probably didn’t realize it. An example of where you would want a temporary table is , as mentioned above , when you are performing temporary calculations and then want to insert this data into a final table. In this instance , a new feature called table-valued parameters in SQL 2008 can help.
TVP’s allow you to instantiate a table variable and then pass that to a stored procedure , which can use it to perform an insert. Lets look at a simple example. We start by creating a (normal) table.  
CREATE TABLE dbo.People(
PersonID int NOT NULL,
Name varchar(100) NOT NULL,
FavouritePizza varchar(100) NOT NULL) 

We now need to define a table variable type ( in other words , a user defined table type ). We do it this way so that the stored procedure we create will use this as a parameter, and will know what type of table variable to expect. Let us define a user defined table type to hold the information we need.

— Create Type
(PersonID int, Name nvarchar(100), FavouritePizza nvarchar(100));

Now for what is essentially the new part – creating a stored procedure that can accept this type as a parameter.Note that the parameter is READONLY.

CREATE PROCEDURE NewPerson (@PersonDetails PeopleTableType READONLY)
SELECT * FROM @PersonDetails

We are now ready to call the stored procedure and pass a table variable to it . In this example , we declare a table variable of the type we created above, populate the table variable with some records, and then send it to the stored procedure, which inserts it into the final table.

DECLARE @NewPeople PeopleTableType

INSERT INTO @NewPeople VALUES (1,’John McLean’,’Quadro Staggioni’)
INSERT INTO @NewPeople VALUES (2,’Bob Smith’,’Vegetarian’)
INSERT INTO @NewPeople VALUES (3,’Ted Connery’,’Greek’)

EXEC NewPerson @NewPeople

And that’s your bulk load with a stored procedure without using any temp tables !

Categories: SQL Server
  1. Christo
    July 2, 2008 at 1:34 pm

    Thanx for the great post. This is definitly going to be very usefull in future development.

  2. Markus
    July 3, 2008 at 6:53 am

    Or you could just provide the new output clause in SQL 2008 instead of executing the sproc again at the end.
     output inserted.personid, inserted.name, inserted.favouritepizza

  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: