Skip to Content

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)…

12 Comments

  • Very nice indeed, forgot about that one

  • 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?

  • Matt Shannon

    Dec 06, 2007 at 08:03 PM

    Holy crap, that is nice.

  • 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 🙂

  • 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 🙂 ]

  • 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?

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

  • 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;

  • 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. 🙂

  • it

    Dec 29, 2008 at 06:48 AM

    nice one. thanks

    visit me at my new blog
    http://sql-insights.blogspot.com/

  • Bharati

    May 17, 2011 at 01:38 AM

    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

Receive comment updates via RSS

Leave a Comment