Partager via

Introducing Table-Valued Parameters - Part 1

  • Article

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

Comments

  • Anonymous
    August 15, 2008
    PingBack from http://informationsfunnywallpaper.cn/?p=866

  • Anonymous
    August 15, 2008
    Thanks Himanshu... Keep me posted and I'll create a series of Silverlight streaming Webcasts for these.  Salute,    Mark Wisecarver    Technical Evangelist

  • Anonymous
    August 15, 2008
    This sounds like a better feature than it will turn out to be in the end.  I remember when Oracle introduced this feature a decade ago and it just never became a common feature to use in all that time.

  • Anonymous
    August 15, 2008

  1. You deserve to ... for such feature
  2. How to work with this...feature from .NET?
  3. Some "smarties" used such feature in some Oracle app. And I was involved to build .NET "bridge" for it... It was a disaster...

  • Anonymous
    August 17, 2008
    Table Valued Parameters are incredibly useful.  We have a number of apps where we previously had to pass in comma separated lists of IDs (e.g. list of Customer IDs) and then parse the strings and turn them into temporary tables so we could do joins on them. It would be really neat if there were some built in tables types defined so we didn't have to create types like these every time: CREATE TYPE IntList_TableType AS TABLE (ID int not null) CREATE TYPE BigIntList_TableType AS TABLE (ID bigint not null)

  • Anonymous
    August 20, 2008
    .NETTCP:BufferManagementASP.NetLoadTestingandOptimizationToolkit-Soyouwanttobeahe...

  • Anonymous
    August 20, 2008
    .NET TCP: Buffer Management ASP.Net Load Testing and Optimization Toolkit - So you want to be a hero

  • Anonymous
    September 17, 2008
    The comment has been removed

  • Anonymous
    January 23, 2009
    IDCzFx  <a href="http://kklmaanqjbgq.com/">kklmaanqjbgq</a>, [url=http://oenzopxvjrfp.com/]oenzopxvjrfp[/url], [link=http://rqccweciopkk.com/]rqccweciopkk[/link], http://hasqfgeztdzw.com/

  • Anonymous
    March 16, 2009
    when you define a table,what are main parameters that you are considering? plese send a anser from mail to dewamax@gamil.com

  • Anonymous
    January 13, 2011
    TVPs have helped our team overcome 2100 parameter limit inherent in SqlCommand and stored procedures. Not only that, our reporting SPs take variable number of parameters in where clause dynamically generated. TVPs helped pass a varaible number of parameters to sp_executesql otherwise not possible as neatly.

  • Anonymous
    January 18, 2011
    An error comes when i make a table-Vaued table that incorrect syntax near as.Tell me the solution of this problem i check my query many times bt same error occur again and again