Hello Folks,

SQL Server Table Valued Parameter is a new parameter type which has been introduced with the arrival of SQL Server 2008.

We can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Steps for Creating Table-Valued Parameters:

Following are the steps as well as with the appropriate code-

Step 1: Create a table type and define the table structure.

Step 2: Create a Stored Procedure which has a parameter of the table type.

Step 3: Now, declare a variable of the table type, and reference the table type.

Step 4: Fill the table variable by using an INSERT statement.

Step 5: Now it’s time to EXECUTE the stored procedure.

If you want to check whether the data is being inserted or not, use the SELECT query:

The result is:


Benefits of Using Table-Valued Parameters:

  1. It enables us to include complex business logic in a single routine.
  2. It does not acquire any kind of locks for the initial population of data from a client.
  3. It provides a simple programming model.
  4. It also reduces the round trips to the server.

Drawbacks of Using Table-Valued Parameters:

  1. SQL Server is not been able to maintain the statistics on columns of table valued parameters.
  2. We are not able to perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine. Therefore, Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines.
  3. We 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 or in the INSERT EXEC string or stored-procedure.

Well this was a small description about the Table Valued Parameters.

And also comments on this!!



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook