Table Valued Parameters in SQL Server 2008

Table valued parameters were introduced in SQL 2008. If you've wanted to pass in an array to a stored procedure (and maybe you used an XML document to fake an array) this is an interesting alternative. Join Plamen for a quick and useful introduction to this new feature.

Plamen Ratchev has over two decades experience in the software architecture and development field. He has enjoyed building solutions for UNIX, DOS and Windows platforms, with main focus on SQL Server since version 4.21. Plamen is founder of Tangra, specializing in relational database applications analysis, implementation, and tuning. His particular interest is in design patterns, performance a...

Ivan Tsatsarov on 12/17/2008

very useful movie

Leo Njampa on 12/17/2008

Very good demo. Very useful too!

Steve Harris on 12/17/2008

very thurough. why is there an equal sign in the exec clause?

Marc Kuyper on 12/17/2008

like the quiz at the end

Peter M. Riggs on 12/17/2008

I'd like to see a syntax example for taking advantage of this in .NET and classic ASP.

Plamen Ratchev on 12/18/2008

Peter, I have a more detailed example on my blog that shows how to use table valued parameters from .NET. Note that only .NET 3.5 or later supports table valued parameters. Here is the link: http://pratchev.blogspot.com/2008/04/table-valued-parameters.html

Plamen Ratchev on 12/18/2008

Steve, this is a standard way to executed stored procedure explicitly assigning input parameters to the correct parameter name. You can simply invoke the stored procedure without using the assignment: EXEC InsertLoans @NewLoans;. But it is a good practice to specify parameter names especially when you have multiple parameters.

Dharmendrakumar on 1/5/2009

Perfect...

David Roach on 4/1/2009

I'm anxious to see it used in ASP.NET or a windows program.

Dean Gross on 2/9/2010

it would be better if it contained more examples of the actual benefit of this feature. I'm still not sure when it would be used.

Plamen Ratchev on 2/10/2010

Dean, this can be used in many places. The biggest benefit is for client side application where you can directly send table as parameter, rather than some concatenated list or XML. I provided one example here: http://pratchev.blogspot.com/2008/04/table-valued-parameters.html. Also, it can be used to pass data between stored procedures, etc.

Michel Archambualt on 5/15/2010

Great to know.

Leonard Peoples on 6/1/2011

Please increase the volume of all jumpstart/sqlshare videos.

Yelena on 6/1/2011

It's a great topic, but not great presentation. I would have to watch it 2 of 3 times to understand what this is about.

Paul E Richmond on 6/1/2011

Concise and complete treatment of the subject.

David Hurley on 6/1/2011

good job

Russell Todd on 6/1/2011

a vb.net example would be nice

Talha on 6/1/2011

I think in this video the host should have explained the benefits more clearly with examples.

MMoore on 6/1/2011

perhaps slow down talking to make the instruction more comprehensible

Eduardo on 6/1/2011

It would be great to create a video showing how to send a table valued parameter from c#

David Burnett on 6/1/2011

Really good. learned something I can use today.

Peter Voutov on 6/2/2011

Can an existing table be used as a 'type' or does a table type have to be declared?

Harish Wadhwa on 6/3/2011

There should be a way to download the code so we can test it.

Plamen Ratchev on 6/3/2011

Peter, you cannot use an existing table, a table type has to be declared.

Plamen Ratchev on 6/3/2011

Harish, under References there is a script link that allows you to download the code.

Ahmad Elayyan on 6/5/2011

same with ORACLE ... sql server is not so enhanced

John O'Sullivan on 6/15/2011

very useful tip

Shahnoza on 6/21/2011

Informative.

Maurice Ivory on 11/4/2011

This is good stuff, interesting how to pass a variable through a table.

chandrasekhar on 1/20/2012

useful video on Table Valued Parameters