Table valued parameters using User-Defined Data Types

User-Defined Data Types can be used as a reusable memory table schema.

Here is where the types are stored in a database

User-Defined Table Types

Simple example

— Create Table Type
CREATE TYPE StateTbl AS TABLE
( StateID INT
, StateCode VARCHAR(2)
, StateName VARCHAR(200)
);

— Create test procedure
CREATE PROCEDURE SortStates @S StateTbl READONLY
AS
SELECT  StateName
FROM    @s
ORDER BY StateName
RETURN 0;
GO

— Insert data and run procedure
DECLARE @p AS StateTbl
INSERT  @p
VALUES  (1, ‘NC’, ‘North Carolina’)
, (2, ‘VA’, ‘Virginia’)
, (3, ‘CO’, ‘Colorado’);

EXEC SortStates @p

Here is a video that shows how to use it https://www.youtube.com/watch?v=Ewf5bivTKdI

Leave a Reply

Your email address will not be published. Required fields are marked *