Introducing Table-Valued Parameters - Part 1

Hey everyone!

I am Himanshu. I am a program manager in the ADO.Net team. Today I am going to talk about Table-Valued parameters that are new in SQL Server 2008. About 4 years back I was writing an application which involved adding metadata about my music library into a database. The database had a table each for storing Artists, Albums, and Songs. While adding an Album of songs into the database I had to insert data into each of these tables. I had defined stored procedure for inserting data into each of the tables. In the first iteration there was a bug in the code due to which these operations were not being done in a single transaction. Needless to say this bug surfaced as a data-corruption issue later on. Once identified the fix was trivial, ensure that all commands are executed in a single transaction. However, what I would have really liked to do was to be able to call a single stored procedure to which I could pass the metadata about the Album and the list of songs at the same time. But there was no good way of accomplishing this back then. I was programming against SQL Server 2000 back then. I am excited to say that now with the new table-valued parameters in SQL Server 2008 I can finally write a single stored procedure that will let me accomplish this without any kind of hacks!

Table-Valued parameters, as the name suggests lets you pass a table as parameter to a stored procedure. In order to use table-valued parameters you need to define a table type and then use the table type in the definition of the stored procedure. Let me illustrate this with the help of an example. I am going to first create a table type named Songs_TableType with two columns, Title and TrackNumber. Then I will use it in a stored procedure to pass a table as parameter.

First creating the table type

Create Type Songs_TableType as Table
(Title nvarchar(120) not null,
TrackNumber int)

I can now use this type in a stored procedure to pass a table as a parameter. The following T-Sql shows how to define a stored procedure that takes this type as a parameter. Note that I have skipped error handling for brevity.

create procedure AddSongs(
 @ArtistName nvarchar(120), 
 @AlbumName nvarchar(120), 
 @Songs Songs_TableType READONLY)
as
begin
 -- Add the Artist
 Declare @ArtistID int
 insert into Artists values (@ArtistName)
 select @ArtistID = SCOPE_IDENTITY()

 -- Add the Album
 Declare @AlbumID int
 insert into Albums values (@AlbumName, @ArtistID)
 select @AlbumID = SCOPE_IDENTITY()

 -- Insert songs
 insert into Songs 
 select title, trackNumber, @AlbumID, @ArtistID
 from @Songs
end 

In the next post I will go into more details into how I can use the stored procedure defined above to simplify the code I have on the client. Till then, adios!

Himanshu Vasishth
Program Manager
ADO.NET