SQL Server Table Valued Parameter

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.

USE School
CREATE TYPE Piyush_TableType AS TABLE
(
[SID] INT, 
Name VARCHAR(50),
City VARCHAR(20),
[State] VARCHAR(30)
)

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

CREATE PROCEDURE Person_Insert
(
@MyTable Piyush_TableType READONLY
)
AS
INSERT INTO Person(Person_ID, Name, City, [State])
SELECT [SID],Name, City, [State]
FROM @MyTable;

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

DECLARE @Students Piyush_TableType

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

INSERT INTO @Students VALUES (1, 'Piyush', 'Bilaspur', 'Chhatissgarh')
INSERT INTO @Students VALUES (2, 'Prashant', 'Bhilai', 'Chattissgarh')
INSERT INTO @Students VALUES (3, 'Harsh', 'Kolkata', 'West Bengal')
INSERT INTO @Students VALUES (4, 'Ankit', 'Dhanbaad', 'Jharkhand')

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

EXEC Person_Insert @Students

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

SELECT * FROM Person

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!!

Regards

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