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!