Table-Valued Parameters is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 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.
- We can now pass the variable to the procedure.
For Example,
Let’s create a Department Table and pass the table variable to insert data using procedure. In our example we will create Department table and afterward we will query it and see that all the content of table value parameter is inserted into it.
Department:
CREATE TABLE Department
(
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(30)
)
GO
1. Create a TABLE TYPE and define the table structure:
CREATE TYPE DeptType AS TABLE
(
DeptId INT, DeptName VARCHAR(30)
);
GO
2. Declare a STORED PROCEDURE that has a parameter of table type:
CREATE PROCEDURE InsertDepartment
@InsertDept_TVP DeptType READONLY
AS
INSERT INTO Department(DepartmentID,DepartmentName)
SELECT * FROM @InsertDept_TVP;
GO
Important points to remember :
– Table-valued parameters must be passed as READONLY parameters to SQL routines. 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.
3. Declare a table type variable and reference the table type.
DECLARE @DepartmentTVP AS DeptType;
4. Using the INSERT statement and occupy the variable.
INSERT INTO @DepartmentTVP(DeptId,DeptName)
VALUES (1,'Accounts'),
(2,'Purchase'),
(3,'Software'),
(4,'Stores'),
(5,'Maarketing');
5. We can now pass the variable to the procedure and Execute.
EXEC InsertDepartment @DepartmentTVP;
GO
Let’s see if the Data are inserted in the Department Table
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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
87 Comments. Leave new
Folks i am have with the following questions.
I have a loading process and insert 1000 records and I’m timeout problem in my database because I send line by line List’ for the procedure of inserting and so will insert line by line, the problem is that connection timeout with the database reaches for the 200 line.
To solve this I converted the List in DataTable and to make the process via BulkCopy in my procedure sends a DataTable to insert a procedure I created a variable of type @Table worked perfect and fast.
My question is in terms of tunning database is bad or good ? Use the type variable @ Table is not performant? Popular uses variable with 1000 rows with 5 columns is to destroy the tunnning of the database and the DBA’s not like any of it? Being that this process will run 1 time a day?
I look forward to all comments.
I think using table-valued parameter the way you did with a table variable is fine Thomas. Worse can happen is you change your variable table to a temptable if your pressure on RAM is too high but I doubt it.
You should be off the hook performance wise.
Thanks you for information. have a nice day.
i am getting the error ‘Incorrect syntax near the keyword ‘AS’.’ when i try to create the type as table..
CREATE TYPE DeptType AS TABLE
(
DeptId INT, DeptName VARCHAR(30)
);
GO
Many thanks! You’ve helped me out a few times with what you’ve written here on your blog. :)
Hello Pinal,
I have one oracle Type which is written as “TYPE APC7_dates_record IS RECORD”, I need to convert this to SQL Server 2008.
What should be the alternative for RECORD in SQL Server. I searched for the same but could not locate the alternative.
Could you help me with the same
Thanks in advance!!
—
Best Regards,
Chetan Navale | Atos India | [email and phone no removed]
as far as my experience to SQL 2005 conversion from oracle 10G,we declared all the rowtype variables as individual ones.
Do not get surprised about the lines of code in would drink or the processing time.
Hi,
I am trying to create a table using stored procedure but I am passing tablename as parameter. It is just throwing error that Incorrect Syntax near @Name. Could you please help me on this one.
Create PROC sp_tablecreation
@Name nvarchar(20)
AS
Create Table @NAME
(
Fname nvarchar(40),
Lname nvarchar(40),
Email nvarchar(40),
Constraint PK_Email Primary Key(Email)
)
GO
I have created table type, one of its column type is varchar(max) which is creating issue and give me error.
is there any limitation of table type ?
What was the error you got?
Pinal, u rock… Excellent explanation… Thanks.. :)
Look like UDT is not recomended though it improves performance. It is hard to maintain and it is worse if you want ALTER/DROP UDT when it is referenced in other SPs.
Any option available to ALTER/DROP UDT without touching its referenced objectes?
Please let me know.
Thanks
How to update records using Table Type can you give example
how to Update table using Table Type Can you give example of that
Thanks pinal,can i use normal parameters like varchar,int,datetime etc.. with table value parameter. if it is so example please
Thank you, its very use full
its great article,
and also I need to know how to call or execute from Asp.Net code.
thanks and regards,
venkat.
hi Pinal,
How to Generate script for view dependencies of user defined table types ?
How can i create a stored proc which accepts other parameters along with a table valued parameter? How can i drop a table valued paramater?
Hi All,
I have list of sqlparameter over 1000’s.can i convert them to redundant or non-redundant table in sql server.Sqlparameter is passed as array of listsize[10000].
Hi Pinal,
I have a situation where I need to create a procedure where I will create a temp table that will have columns from different permanent tables. Now when we change the data size in some permanent table, we need to change the data size in the Temp table spec.
I want to avoid this. Is their any way of doing this.
Note : Temp table will refer to different tables, and might contain some extra columns.
DECLARE @Dept_TVP AS DeptType;
INSERT INTO @Dept_TVP(DeptId,DeptName)
VALUES (1,’IT’)
UPDATE D
SET DepartmentName = TV.DeptName
FROM Department D
INNER JOIN @Dept_TVP TV ON TV.DeptId = D.DepartmentID
SELECT * FROM Department WHERE DepartmentID = 1
Why the table type parameters are read only? any specific reasons behind this?