Use table values as input parameter examples in SQL Server stored procedures _mssql

Before 2008 if we want to pass a table as an input parameter to a SQL Server stored procedure makes it more difficult, it may take a lot of logical processing to pass these table data as strings or XML.

Table-valued parameters are provided in 2008. With table-valued parameters, you can send multiple rows of data to a Transact-SQL statement or routine, such as a stored procedure or function, without having to create a temporary table or many parameters, which eliminates many custom code. Such an operation becomes very easy to operate on a table-function-based operation within a stored procedure.
Table-valued parameters are declared using user-defined table types. So you define the table type before you use it.

/* Create a table type. *
/create type Locationtabletype
as table (LocationName VARCHAR)
, 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
 
 
/* declares a table-valued parameter variable. * *
DECLARE @LocationTVP aslocationtabletype;
 
/* Inserts data into the table-valued variable
/INSERT into @LocationTVP (LocationName, costrate)
  SELECT Name, 0.00 from
  person.stateprovince;
 
/* Pass the variable to the stored procedure * *
EXEC USP_INSERTPRODUCTIONLOCATION@LOCATIONTVP;
Go

Query table production.location can see that the data has been inserted.