THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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
CREATE TABLE Movie
(
MovieID INT IDENTITY(1, 1),
MovieRatingId INT,
Title VARCHAR(200) NOT NULL
);
GO
INSERT INTO Movie (MovieRatingId, Title)
VALUES
(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
CREATE TYPE MovieRating AS
TABLE
(
MovieRatingId int NOT NULL,
Code varchar(20) NOT NULL,
Description varchar(200) NULL,
AllowYouthRentalFlag bit NOT NULL
)
GO

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

--Declare a variable using our table type
DECLARE @MovieRatings MovieRating
--and insert data into it (again, using a row constructor)
INSERT INTO @MovieRatings
VALUES
(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...
EXEC
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 06, 2007 6:14 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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:

Peter,

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

as

set nocount on

select * from #temp

go

--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 is......run 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!

CREATE TABLE Movies

(isan CHAR(24) NOT NULL PRIMARY KEY

   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'),

              ('PG-13', 'PARENTAL GUIDANCE FOR CHILDREN UNDER 13'),

              ('R', 'RESTRICTED, NO CHILDREN UNDER 17 WITHOUT PARENT'),

              ('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:

Joe,

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.

However...

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

http://sql-insights.blogspot.com/

December 29, 2008 6:48 AM
 

Bharati said:

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

Example:

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)

begin

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

end

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

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks 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 (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement