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