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)…
Cross-posted from SQLBlog! -
http://www.sqlblog.com