What is Table Valued Parameter, in MS SQL Server?

Question

Table-Valued parameters, as the name suggests lets you pass a table as parameter to a stored procedure. In order to use table-valued parameters you need to define a table type and then use the table type in the definition of the stored procedure.

First creating the table type

Create Type Songs_TableType as Table
(Title nvarchar(120) not null,
TrackNumber int)


create procedure AddSongs(
 @ArtistName nvarchar(120),
 @AlbumName nvarchar(120),
 @Songs Songs_TableType READONLY)
as
begin
 -- Add the Artist
 Declare @ArtistID int
 insert into Artists values (@ArtistName)
 select @ArtistID = SCOPE_IDENTITY()

 -- Add the Album
 Declare @AlbumID int
 insert into Albums values (@AlbumName, @ArtistID)
 select @AlbumID = SCOPE_IDENTITY()

 -- Insert songs
 insert into Songs
 select title, trackNumber, @AlbumID, @ArtistID
 from @Songs
end