User Defined Table Type and Table Valued Parameter in SQL Server

dbtut

6 years ago

With User Defined Table Type, we can store the structure of a table. We can use the User Defined Table Typies by defining the table-valued parameter in the Stored Procuder or Functions. We can use these structures instead of specifying the temporay table in the Stored Procedure or Function.

Below you can see the sample usage. You can find explanations in the scripts.

Create User Defined Table Type

USE AdventureWorks2008R2;
GO
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

USE AdventureWorks2008R2;

CREATE TYPE LocationTableType AS TABLE

( LocationName VARCHAR(50)

, CostRate INT );

Using User Defined Table Type in a Stored Procedure by using table-valued parameter

Table valued parameter in this example is @TVP

CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

CREATE PROCEDURE usp_InsertProductionLocation

@TVP LocationTableType READONLY

SET NOCOUNT ON

INSERT INTO [AdventureWorks2008R2].[Production].[Location]

([Name]

,[CostRate]

,[Availability]

,[ModifiedDate])

SELECT *, 0, GETDATE()

FROM @TVP;

Define a variable of the type of table type that we created

DECLARE @LocationTVP 
AS LocationTableType;

1 2	DECLARE @LocationTVP AS LocationTableType;

Insert data to this variable

INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks2008R2].[Person].[StateProvince];

INSERT INTO @LocationTVP (LocationName, CostRate)

SELECT [Name], 0.00

FROM

[AdventureWorks2008R2].[Person].[StateProvince];

Execute the stored procedure by passing this variable as a parameter

EXEC usp_InsertProductionLocation @LocationTVP;
GO

1 2	EXEC usp_InsertProductionLocation @LocationTVP; GO

Source <https://msdn.microsoft.com/en-us/library/bb510489(v=sql.105).aspx>