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