The TABLE custom type / Using a TVP - Table Valued Parameter

Some Code to demonstrate

1. Creating a custom TABLE type
2. Passing a Table as a parameter (Table Valued Parameter)

In reality my 80s themed example would be far more exciting...

SET NOCOUNT ON;
GO

-- Create Target Table
CREATE TABLE EightiesHits
  (Artist VARCHAR(50)
  ,Title VARCHAR(50)
  ,ReleaseYear NUMERIC(4,0))
GO

-- Create Custom Type
CREATE TYPE Release AS TABLE
  (Artist VARCHAR(50)
  ,Title VARCHAR(50)
  ,ReleaseYear NUMERIC(4,0));
GO

-- Create Stored Procedure to demonstrate passing a table valued parameter.
CREATE PROCEDURE dbo.AddToCollection
    @TVPRelease Release READONLY
AS 
  SET NOCOUNT ON
  INSERT INTO EightiesHits
          (Artist,Title,ReleaseYear)
  SELECT Artist,Title,ReleaseYear
  FROM  @TVPRelease;
GO

-- Declare TVP
DECLARE @EightiesTunesTVP AS Release;

-- Put some data in!
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Do They Know It''s Christmas?', 'Band Aid', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('I Just Called to Say I Love You', 'Stevie Wonder', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Relax', 'Frankie Goes to Hollywood', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Two Tribes', 'Frankie Goes to Hollywood', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Careless Whisper', 'George Michael', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Last Christmas / Everything She Wants (Remix)', 'Wham!', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Hello', 'Lionel Richie', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Agadoo', 'Black Lace', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Ghostbusters', 'Ray Parker, Jr', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Freedom', 'Wham!', 1984)

-- Now pass te contents of TVP @EightiesTunesTVP 
-- to table EightiesHits via stored procedure AddToCollection
EXEC AddToCollection @EightiesTunesTVP;

-- Now prove the data is there
SELECT * FROM EightiesHits

--Clean up
DROP TABLE EightiesHits;
DROP PROCEDURE AddToCollection;
DROP TYPE Release;
GO