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:
- It enables us to include complex business logic in a single routine.
- It does not acquire any kind of locks for the initial population of data from a client.
- It provides a simple programming model.
- It also reduces the round trips to the server.
Drawbacks of Using Table-Valued Parameters:
- SQL Server is not been able to maintain the statistics on columns of table valued parameters.
- 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.
- 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!!