Example of using table values as input parameters in SQL server stored procedures

Time:2021-4-13

Before 2008, if we want to pass the table as an input parameter to the SQL server stored procedure, it will be more difficult. We may need a lot of logic processing to pass the table data as string or XML.

Table valued parameters are provided in 2008. Using table valued parameters, you can send multiple rows of data to transact SQL statements or routines (such as stored procedures or functions) without creating temporary tables or many parameters, which can save a lot of custom code. Such operations become very easy to operate for table function based operations in stored procedures.
Table valued parameters are declared using a user-defined table type. So you need to define the table type before using it.

/*Create a table type*/
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
 
/*Create a stored procedure with table valued parameters as input*/
CREATE PROCEDURE dbo. usp_InsertProductionLocation
  @TVP LocationTableType READONLY
  AS
  SET NOCOUNT ON
  INSERT INTO Production.Location
      (Name
      ,CostRate
      ,Availability
      ,ModifiedDate)
    SELECT *, 0, GETDATE()
    FROM @TVP;
    GO
 
 
/*Declare a table valued parameter variable*/
DECLARE @LocationTVP ASLocationTableType;
 
/*Insert data into a table valued variable*/
INSERT INTO @LocationTVP(LocationName, CostRate)
  SELECT Name, 0.00
  FROM Person.StateProvince;
 
/*Passing variables to stored procedures*/
EXEC [email protected];
GO

Query table Production.Location You can see that the data has been inserted.