THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

2008: Row Constructor or Table-Valued Parameter

Denis posted a follow-up to the posts by both Louis and me. However, I have to say that there is a better way to pass a bunch of data to a procedure. The following code is amended to include the table-valued parameter feature.

--The Movie Table
MovieRatingId INT,
INSERT INTO Movie (MovieRatingId, Title)
(3, 'SQL the Movie'),
(4, 'SQL Massacre'),
(1, 'SQL for Everyone'),
(4, 'SQL Massacre 2 - The Oracle Returns');

--Create a new table type in the database
MovieRatingId int NOT NULL,
Code varchar(20) NOT NULL,
Description varchar(200) NULL,
AllowYouthRentalFlag bit NOT NULL

--Create a new proc that accepts a table as a parameter
CREATE PROC prMoviesByRating
    @MovieRatings MovieRating READONLY
--and join to that table-valued parameter
        MR.Code AS RatingCode,
        MR.Description AS RatingDescription
    FROM Movie AS M
        INNER JOIN @MovieRatings AS MR
            ON M.MovieRatingId = MR.MovieRatingId;

--Declare a variable using our table type
DECLARE @MovieRatings MovieRating
--and insert data into it (again, using a row constructor)
INSERT INTO @MovieRatings
(0, 'UR','Unrated',1),
(1, 'G','General Audiences',1),
(2, 'PG','Parental Guidance',1),
(3, 'PG-13','Parental Guidance for Children Under 13',1),
(4, 'R','Restricted, No Children Under 17 without Parent',0)

--and pass the table as parameter to the proc...
prMoviesByRating @MovieRatings

So when I want to pass the data in, no dynamic SQL is necessary, since I can instead simply pass the table itself (with all of its data)…

Published Thursday, December 6, 2007 6:14 PM by Peter W. DeBetta



Denis Gobo said:

Very nice indeed, forgot about that one

December 6, 2007 6:23 PM

Denis Gobo said:

One question I have is can you run this proc from for example ColdFusion or Java

Does jdbc support table value parameters, and if not will it?

December 6, 2007 6:28 PM

Matt Shannon said:

Holy crap, that is nice.

December 6, 2007 8:03 PM

Denis Gobo said:


I can see this being handy when passing data from server to another linked server (if it works)

However when you are on the same server you can just use a local temp table

--create proc

create proc TestProc


set nocount on

select * from #temp


--create our temp table

create table #temp (id int)

insert #temp values(1)

insert #temp values(2)

insert #temp values(3)

--exec proc

exec TestProc

BTW, this is one of the in person interview questions I like to ask "Is a local table available inside a stored proc when called?"

and the answer the code and find out  :-)

December 7, 2007 11:35 AM

Peter W. DeBetta said:

Matt - Well said!

Denis - There are some advantages, such as table-valued parameters do not acquire locks for the initial population of data from a client (even if in T-SQL) and they do not cause a statement to recompile (quoted from BOL). Also, relying on the fact that #temp exists when the procedure is executed is not a good programming model, espceially when compared to the ability to persist the table def (strongly typed, so to speak) in the procedure. [Not that I've never done what you have presented here :-) ]

December 7, 2007 12:02 PM

Joseph Baggett said:

I see the value inside the database of using this.

I am curious about how you would pass a collection from a programming language, like C#, using this methodology, which is outside of the database?  Is it possible?  Or do I need to still go down the route of passing in xml and converting it to a table?

December 20, 2007 12:43 PM

Anastasiosyal said:

December 21, 2007 7:43 AM

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM

Joe Celko said:

I gotta be pedantic!

Can you explain why there is only one movie, as shown by the singular name? What is a “movie_rating_id” as opposed to a mere “movie_rating”?  Why you want to use numbers for a well-understood encoding scheme?  Why did you use a proprietary physical record locater when there is an industry standard? ISAN is a voluntary numbering system for the identification of audiovisual content, including works and versions of works. The ISAN is a 96-bit number comprised of three segments: a root, an episode or part, and a version. When the 96-bit ISAN is represented in hexadecimal form it has 24 digits (made up of the numbers 0-9 and the letters A-F):

Let's bring the table up to spec and add X-rated movies – Hey I might want to use this database, too!



   CHECK (isan LIKE '[0-9A-F][0-9A-F][0-9A-F] ..[0-9A-F]'),

movie_title VARCHAR(200) NOT NULL,

movie_rating CHAR(5) NOT NULL

 CHECK (movie_rating IN ('UR', 'G', 'PG', 'PG-13', 'R', 'X'))


Using a table constructor avoids both dynamic SQL and proprietary table passing.  I don't have 2008 on the machine at work, so this is untested.

SELECT M.movie_title, M.movie_rating, R.rating_description

 FROM Movies AS M,

      (VALUES ('UR', 'UNRATED'),

              ('G', 'GENERAL AUDIENCES'),

              ('PG', 'PARENTAL GUIDANCE'),



              ('X', 'ADULT CONTENT')) AS R(movie_rating, rating_description)

WHERE M.movie_rating = @my_movie_rating

  AND R.movie_rating = @my_movie_rating;

December 28, 2007 3:23 PM

Peter W. DeBetta said:


The purpose of this post was to follow up on an existing example and to show the technology of table-valued parameters.

I will admit that the CHAR(5) (movie_rating) field should have been used instead of the INT (movie_rating_id) for the primary key of the MovieRating table type.


From the ISAM website: "The ISAN is not a 'content descriptor'. It is a 'dumb' number, meaning that it does not include any codes or other signifying elements. Its purpose is to identify the work with a unique number, not to provide any type of descriptive information about the work. "

In other words, it is a centralized system for surrogate values.

So the ISAN standard is voluntary, and not all audiovisual works comply. Since any movie without an ISAN would also need to have a surrogate ISAN created, we would be creating surrogates for an already surrogate system. And so, Joe, I'd like to know: What is your argument for using a surrogate CHAR(24) instead of a surrogate INT?

That being said, since an ISAN is really a BINARY(12) value, why not use BINARY(12) as the primary key field? Then you could either have a computed column with the Human readable version (which is actually 33 characters when you include dashes and the 2 check characters) or just do such a conversion as needed for display.

You also forgot the 'NC-17' movie rating. :-)

December 28, 2007 4:44 PM

it said:

nice one. thanks

visit me at my new blog

December 29, 2008 6:48 AM

Bharati said:

Hi very nice.I hav one doubt .How to pass scalar variables through table valued parameters.


create table emp(id int identity(1,1),name varchar(100))

create type emptype as table (id int identity(1,1),name varchar(100))

create procedure usp_emptype(@tablevariable emptype readonly)


insert into emp(name ) values (name=@name)


Executing this procedure i am getting like this error:

Msg 156, Level 15, State 1, Procedure usp_emptype, Line 2

Incorrect syntax near the keyword 'begin'.

Msg 102, Level 15, State 1, Procedure usp_emptype, Line 3

Incorrect syntax near '='.

Please let me know What's the solution

May 17, 2011 1:38 AM
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement