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
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)
Declare
a STORED PROCEDURE that has a parameter of table type:
CREATE PROCEDURE InsertEmployee
@InsertEmp_TVP EmployeeType READONLY
INSERT INTO Employee(EmpID ,FirstName,MiddleName,LastName,EmailId)
SELECT * FROM @InsertEmp_TVP;
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)
(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.
·
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.
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.
