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 CREATE TYPE PeopleTableType AS TABLE (PersonID int, Name nvarchar(100), FavouritePizza nvarchar(100)); GO

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) As BEGIN INSERT INTO dbo.People SELECT * FROM @PersonDetails END

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 !