Table values parameters
How we can pass multiple rows in stored procedure or function as a parameter?
Table-valued parameters are a new parameter type in SQL 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 Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters. This is very helpful when you have requirement to pass dataset to stored procedure or function to manipulate internally. Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations. In earlier versions of SQL SERVER this is not possible to pass table variable in stored procedure or function. it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 or later version we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.
Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:
- Create a table type and define the table structure
- Declare a stored procedure that has a parameter of table type.
- Declare a table type variable and reference the table type.
- Using the INSERT statement and occupy the variable.
- After the table variable is created and filled, you can pass the variable to a routine.
- After the routine is out of scope, the table-valued parameter is no longer available. The type definition remains until it is dropped.
Let us work with an example:-
Create Employee Table and pass the table variable to insert data using procedure. In our example we will create Employee table and then we will query it and see that all the content of table value parameter is inserted into it.
Create Table Employee
(
EmpID int Primary key,
FirstName varchar(100),
MiddleName varchar(100),
LastName varchar(100),
EmailId varchar(100)
);
GO
Create a TABLE TYPE and define the table structure:
CREATE TYPE EmployeeType AS TABLE
(
EmpID int,FirstName varchar(100),MiddleName varchar(100),LastName varchar(100),EmailId varchar(100)
);
GO
Declare a STORED PROCEDURE that has a parameter of table type:
CREATE PROCEDURE InsertEmployee
@InsertEmp_TVP EmployeeType READONLY
AS
INSERT INTO Employee(EmpID ,FirstName,MiddleName,LastName,EmailId)
SELECT * FROM @InsertEmp_TVP;
GO
Points to be remember before using table type:-
- Table-valued parameters must be passed as READONLY parameters to SQL routine. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
- You 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.
Declare a table type variable and reference the table type.
DECLARE @Employee_TVP AS EmployeeType;
INSERT INTO @Employee_TVP(EmpID ,FirstName,MiddleName,LastName,EmailId)
VALUES
(1,'Arun',null,'Kumar','arun@tsqlcirclelive.com'),
(2,'Laxmi','Kant','Azad','laxmi@tsqlcirclelive.com'),
(3,'Rakesh','Kumar','sharma','rkshrma@tsqlcirclelive.com'),
(4,'abhi',null,'parihar','abhipar@tsqlcirclelive.com'),
(5,'Ronald',null,'Disuja','laxmi@tsqlcirclelive.com')
EXEC InsertEmployee @Employee_TVP;
Let’s see if the Data are inserted in the Employee Table
Benefits
Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits:
· Do not acquire locks for the initial population of data from a client.
· Provide a simple programming model.
· Enable you to include complex business logic in a single routine.
· Reduce round trips to the server.
· Can have a table structure of different cardinality.
· Are strongly typed.
· Enable the client to specify sort order and unique keys.
Restrictions
Table-valued parameters have the following restrictions:
· SQL Server does not maintain statistics on columns of table-valued parameters.
· Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
· You 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.
Scope
A table-valued parameter is scoped to the stored procedure, function or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.
Security
Permissions for table-valued parameters follow the object security model for SQL Server, by using the Transact-SQL keywords: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION, and REVOKE.
Conclusion
Table-Valued Parameters is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex business logic in single routine. They reduce Round Trips to the server making the performance better.
