Table Valued Parameters Save Code and Traffic

  • Article

690px-Microsoft_SQL_Server_Logo.svgDo you use Stored Procedures? I hope so, they are great for performance and security. But, on early versions of SQL Server you could only pass one record at a time to a stored procedure. So those of us who nobly followed best practice and used stored procedures for inserting, updating, and deleting records often found ourselves having to write loop logic to call the same stored procedure over and over to insert, update, or delete multiple records. With Table Valued Parameters you can pass a set of records to a stored procedure. Yay! This feature was long overdue and is one of my favourite developer features added in SQL Server 2008.

In SQL Server 2005, if you had a table that contained a list of Hockey Players, and you wanted to load three records into that table using a stored procedure, your code would look something like this:

CREATE TABLE HockeyPlayer
(id       INT                 NOT NULL,
name VARCHAR(50) NULL,
team  VARCHAR(50) NULL)
GO
 
CREATE PROCEDURE InsertPlayer
(@id int, @name VARCHAR(50), @team VARCHAR(50))
AS
INSERT HockeyPlayer (id, name, team)
VALUES (@id, @name, @team)
GO
 
EXECUTE InsertPlayer 1,’Michel’,'Ottawa'
EXECUTE InsertPlayer 2,’Mike’,’Toronto’
EXECUTE InsertPlayer 3,’Alexei,’Vancouver’

We had to execute the stored procedure once for every record we wanted to insert.

In SQL Server 2008, they added a new feature called Table Valued Parameters. This feature allows you to pass a one or more records to the stored procedure. In order to create a stored procedure using Table Valued Parameters, you must perform two steps.

1. Create a Table Type in the database that defines the structure of the records you will pass to the stored procedure.

CREATE TYPE PlayerTableType AS TABLE
(id INT, name VARCHAR(50), team VARCHAR(50))

2. Create your stored procedure and declare an input parameter with the type set to the Table Type you created. This input variable must be declared as READONLY. (I know my stored procedure isn’t very useful, but you get the idea)

CREATE PROCEDURE InsertManyPlayers (@PlayerRecords PlayerTableType READONLY)
AS BEGIN
   INSERT INTO HockeyPlayer (id, name, team)
   SELECT * FROM @PlayerRecords
END

Now you have a stored procedure that can accept a record set. Now how do you call it?

Calling your stored procedure from T-SQL

To call a stored procedure with a table valued parameter from T-SQL you have to:

  1. Create a variable based on your Table Type
  2. Populate that table variable with the records you want to pass to your stored procedure.
  3. Execute your stored procedure, passing in your table variable

DECLARE @MyPlayers PlayerTableType

INSERT INTO @MyPlayers
VALUES
(4,’Ken’,’Calgary’),
(5,’Neil’,’Edmonton’),
(6,’Mitch’,’Toronto’)

EXECUTE InsertManyPlayers @MyPlayers

Calling your stored procedure from .NET

You can pass a DataTable, a Data Reader or any other object that implements the iList interface to a table valued parameter. When you declare the command parameter in .NET you must specify SqlDbType as Structure, and TypeName as the name of your table type, then execute the call to your stored procedure.

In the example below, I create a data table with the same structure as the table type and populate the data table with the records I want to send to the stored procedure.

Dim dt As New DataTable("player")
dt.Columns.Add("id", System.Type.GetType("System.Int32"))
dt.Columns.Add("name",System.Type.GetType("System.String")
dt.Columns.Add("team",System.Type.GetType("System.String"))

Dim newRow As DataRow = dt.NewRow()
newRow("id") = 7
newRow("name") = "Chris"
newRow("team") = "New York"
dt.Rows.Add(newRow)

newRow("id") = 8
newRow("name") = "Chris"
newRow("team") = "New York"
dt.Rows.Add(newRow)

The DataTable implements the iList interface, so I can pass this data table to a table valued parameter in a stored procedure. The code below defines the SqlParameter that will pass the data table and executes the stored procedure.

Dim Cmd As New SqlCommand("InsertManyPlayers", myCon)
Cmd.CommandType = System.Data.CommandType.StoredProcedure
Dim tvp As New SqlParameter
tvp.ParameterName = "@PlayerRecords"
tvp.SqlDbType = SqlDbType.Structured
tvp.TypeName = "dbo.PlayerTableType"
tvp.Value = dt Cmd.Parameters.Add(tvp)

myCon.Open()
Cmd.ExecuteNonQuery()

You have now passed multiple records to a stored procedure in the database with a single call using the magic of table valued parameters. SQL rocks Smile.

Of course I can’t forget My 5. This week:

My 5 Places you can go learn something new about SQL Server (in no particular order)

  1. SQLTeach May 30 – June 3, 2011, Montreal, driving distance for Eastern Canada and lots of great content!
  2. TechEd North America May 16-19, Atlanta, don’t forget most of the content is also available online after the conference, even if you did not attend the show!
  3. SQLPass October 11-14th, Seattle, fabulous SQL conference for those on the West Coast.
  4. Greg Low’s blog (this guy knows his SQL Server, and he is a great presenter if you ever get a chance to catch one of his sessions)
  5. SQL Server Developer Center has links to lots of great resources including SQL Server compare editions, to compare features, I frequently get asked what is the difference between Express and Standard, between Standard and Enterprise, this is where I always look it up!

I know many of us are just upgrading to SQL Server 2008 which is why I brought up this particular feature, but I am curious, what do you want to read about in the blog, would you like to be reminded of what is in SQL 2008, or do you want to know what’s new in Denali? Both? Let us know!