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.