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
It looks like there’s a reason to switch to 2008 version. Great article, thank you so much.
Keep a good work man!,
Good article.
I am already using TVP in my Sql objects.
However, unless you do inline Sql on the application layer, .Net seems the only framework to have had this new attribute in the ADO object.
I noticed you were admin on CF server and you might also have some knowledge on Java and php languages …
Do those languages integrate the TPV feature?
What is the syntax for including the table parameter in a where clause?
Example:
SELECT * FROM @InsertDept_TVP where @InsertDept_TVP.DepartmentID = 10
How does one reference the table in the where clause?
Easiest way is to employ aliases.
SELECT * FROM @InsertDept_TVP id
inner join @asecondTVP tvp2 on id.firstTVPColumn = tvp2.secondColumn
where id.DepartmentID = 10
can you please extend your help to use this “update if exists” please please
thx
There is no direct command. You can use
Update t1
set t1.col=t2.col
from table1 as t1 inner join table2 as t2 on t1.keycol=t2.keycol
hi thanks for the reply, just need a little more direction, i am trying to use “updateif exists ” is it possible if so can you please point me to the right direction
thanks again
I have a Table Type defined in a database. It is used as a table-valued parameter in a stored procedure. I would like to call this procedure from another database, and in order to pass the parameter, I need to reference this defined type.
But when I do:
– DECLARE @table dbOtherDatabase.dbo.TypeName
it tells me that:
– The type name ‘dbOtherDatabase.dbo.TypeName’ contains more than the maximum number of prefixes. The maximum is 1.
Could I reference this table type?
Victor,
I have the same need you have detailed. Did you receive a response on your question yet? If so, would be kind enough to share it with me?
not sure if you still need this solution. What I figured out one must do is place the proc or fn in the target db. You instantiate a copy of each tvp you wish to employ “cross-db” within each target. The target proc or fn uses the tvp from its own db which avoids the cross-db limitation.
If you need to “pass” data via tvp from DB_1 to DB_2, call the DB_2 proc from within the DB_ proc/fn.
Hope this helps.
I ran into the same problem as Victor and tried to address it by creating a synonym in the other database. The synonym creates fine but when i try to reference it i get an error that says “Cannot find datatype “.
Hi,
i have some get some error when creating Table Type
error..
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘AS’.
Thanks
Lukma
Which version of SQL Server are you using? This will work from version 2008 onwards
hello…
i want an answer for the following question:
Wat query should we execute to just display first five records from a table?
you can use a top clause in your query so that you get the number of rows in a table
Eg: select top(5) from table
Hello,
Was just reading table valued parameter, and in your introduction you said “we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function ”
Maybe I am missing anything, Here in microsoft artical they said you can’t pass table valued parameter in Function
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
Limitations of Table-Valued Parameters
There are several limitations to table-valued parameters:
*
You cannot pass table-valued parameters to user-defined functions.
Please explain if I misunderstood anything.
Thanks
Aamir
You are right about this article, but the fact is that it does work, and also in SQL Server books online on table valued parameters they no longer mention this limitation although they list the other limitations of the above article.
So what it appears to me is that Microsoft waived the limitation and as of now it works with UDF’s as well, and the above article is simply outdated, and there are many such lurking around in the MSDN.
Thanks.
Hi,
i have some get some error when creating Table Type
Plz anybody help me out……………………………
Error Message
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘AS’.
Thanks
Debasish
You can pass a table-valued parameter to functions:
declare @a ParetoValues2 –table-valued param
Insert into @a
select ROW_NUMBER() OVER(ORDER BY val desc) RowNbr,val
FROM dbo.tmpA
select * FROM dbo.fn_W2( @a)
CREATE type dbo.ParetoValues2 as table ( RowNbr bigint primary key not null,Val float)
Table-valued parameter typesmust be defined in the database you work with. Otherwise you could use dynamic sql but why not defining it in the databae you work?
Debasish, doesnt eem to work , I tried:
use db1
declare @a ParetoValues2
Insert into @a
select ROW_NUMBER() OVER(ORDER BY column_id desc) RowNbr,column_id as val
FROM sys.columns
select * FROM db2.dbo.fn_W2( @a)
but I got an error:
Msg 206, Level 16, State 2, Line 6
Operand type clash: ParetoValues2 is incompatible with ParetoValues2
strange because both are defined the same.
I’ll insert this as a bug in microsoft connect.
Hi,
This is a very useful feature.
Any hint/idea of how to use Table-valued parameters from JAVA?
Also other than TVP, is there any support for using arrays or lists in SQL Server 2008?
For example Oracle and DB2 supports Arrays, we just need to send java.sql.Array parameter as input to the setArray() method of CallbaleStatement API and this is handled as an array at database side.
Is there any thing similar to this, that can be used from Application layer side in java language with SQL Server 2008?
This is very urgent requirement, can some one help?
Thanks,
Swetha.
HI,
Your article is very useful to every one.
I have to pass table valued data type to dynamic query i tried it but i got the error as must declare scalar variable
could any one solve this
thank you,
regards
sathish
I had a similar problem and it was solved when I changed the compatibility to 100.
An error comes when i make a table-Vaued table that incorrect syntax near as.Tell me the solution of this problem i check my query many times bt same error occur again and again
Can you post the function code you used?
I swore I figured out how to do this but I must be losing my mind. Is it possible to declare User-Defined Table Type stored in one DB from another DB?
Example, I would like to save all UDTT in a “Utilities” DB. Could these be declare in other databases such as “Accounting” as follows?:
USE Accounting
{…}
DECLARE @GenJournal Utilities.AcctLedgerType;
Or
DECLARE @GenJournal Utilities.sys.AcctLedgerType;
Or
DECLARE @GenJournal Utilities.dbo.AcctLedgerType;
If so, what is the correct syntax?
Thank you for your contributions to IT.
JG
Hi Pinaldave,
The TVP is good feature in sqlserver 2008. My concern is about Trigger excution. Plz clarify
I am inserting 1 to 5 list of itemcode ,qty in to ‘Item’ table, using insert trigger while inserting item data, updating qty in ‘Stock’ table.
Example.
item lists
————
slno desc qty.
1 A 5
2 B 6
3 C 7
4 D 8
in item has inserted as it is item lists like slno Ascending order (1, 2,3,4)
but stock table affecting like slno (4,3,2,1) descending order
is it restriction with TVP.
great article!!! Easy to understand. Thank You
Hi,
How can i get the column names of User Defiened Table Types