Table-Valued Parameter :

Table-Valued Parameters are a new parameter type in MSSQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued

parameters to send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters.

Steps to write a procedure using Table-Valued Parameter :

1. Create a table data type and define the table structure.
2. Create a procedure with Table-Valued Parameter.
3. Declare a varaible of table data type and insert the data into table daa type.
4. Pass the table data type to procedure.

Benefits :

1. Table-Valued Parameter give better performance than pass list of parameters to stored procedure or passing values using while loop / Cursors.
2. Do not acquire locks for the initial population of data from a client.
3. Enable you to include complex business logic in a single stored procedure.

Limitations :

1. Table-Valued Parameters can only be indexed to support Unique or Primary Key Constraints.
2. Table-Valued Parameters are read only in stored procedure code. Cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in stored procedures.
3. Table-Valued Parameters design can not be modify using Alter Table statement.
4. Table-Valued Parameters can not pass to CLR user-defined functions.
5. MSSQL Server does not maintain statistics on columns of table-valued parameters.
6. Cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO.

Example :

GO

CREATE TYPE UserTableType AS TABLE
( Username VARCHAR(100)
, is_active bit)

GO

-- Creating Stored Procedure
CREATE PROCEDURE Update_Isactive_Users
    @TVP UserTableType READONLY
    AS
Begin
    SET NOCOUNT ON
Update u set is_active = t.is_active from users u join @TVP t on u.username = t.username
END

GO

-- Defing Table Valued Parameters
DECLARE @UserTVP
AS UserTableType

GO

-- Populating data into @UserTVP
INSERT INTO @UserTVP (Username, is_active)
    SELECT 'satya', 1 union all
SELECT 'jyothi', 1 union all
SELECT 'ravi', 1 union all
SELECT 'naveen', 0 union all
SELECT 'suresh', 0 union all
SELECT 'ganesh', 1 union all
SELECT 'sasi', 0
 
GO

-- Executing Procedure by passing the Table Valued Parameters
EXEC Update_Isactive_Users @UserTVP

GO