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 (http://blog.SQLAuthority.com)
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
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
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
http://msdn.microsoft.com/en-us/library/bb675163.aspx
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
This is Excelent, good artical
But i am getting Error
[b]ERROR: No mapping exists from DbType 30 to a known SqlDbType.[/b]
in my code
My VB Code:
dbCommand = objdbFactory.GetStoredProcCommand(“AddSqc”)
objdbFactory.AddInParameter(dbCommand, “@SqcTable”, SqlDbType.Structured, dsFileName) ” @SqcTableis User Defined Table
so I did research on web then I found solution
You can also fine more information about Table valued parameters in Enterprise Liberary
[quote][b]http://entlib.codeplex.com/discussions/215754[/b][/quote]
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
hi,
I created a type with 20 columns. I used this type in many of my procedures.
now i need to add more columns to this type. what is the syntax to alter user defined table type for adding and removing columns and changing datatypes.
Thanks.
there is no options to alter an UDT. you can go for DROP & CREATE. one thing is before dropping you need to DROP all the SP’s Referencing that UDT and create all immediately after Creating the modified UDT.
Thanks Debasish.
But I knew it already. I think this way is not good. there should be an alter command to do that.
Thanks a lot for replying.
Very useful.I did without any error.
Thanks Pinaldave.
i want table valued parameter stored procedure for delete
hi pinal
if i have a component returning an array as an output parameter…do u know of a way in SQL where i can catch it and manipulate it
background…
we have a database in a proprietary format (no DBMS or RDBMS) so to talk to it (or read data from it), the only way is through a component toolkit…which effectively is a COM component…or DLL (to make it easier)…i can see that people at my work place have used the DLL to ask for data and it returns as an array…(its document says the same)…when i say people have used it…they have created dot net web service to access it and then converted it to XML document etc…
now we want to cut down the layer of the web service and need a way to be able to access that data in a SSRS report…so the way i see it is…to be able to create an object of the COM component in a sql procedure (i have used sp_OACreate in the past to instantiate COM components from within SQL Server procedures so i know it works…)
the issue is the COM component returns a variant array and i believe there is no array datatype in SQL to catch it…(or if there is one, i don’t know about it)…
what do u think could be the way to catch it in SQL…a table, or an array (if at all it’s available) or something else…
your help will be highly appreciated.
many thanks
Gaurav
just in relation to the above query…i found out at http://msdn.microsoft.com/en-us/library/aa238864(v=sql.80).aspx that methods can not have output parameters as arrays when we use sp_OAMethod to call them…if that’s the case…then i feel i m kind of stuck…
A means around the update limitation on TVP’s used as params (READONLY) is to declare an identical TVP within the body of the function (in the case of multi-statement tvf’s) or the proc and deposit the contents into the internally declared TVP as needed. Then one may insert, update, delete away all one wishes. I use this method extensively in lieu of table params. There appears to be a noticeable performance superiority of TVP’s over their table var counter-parts. This is especially true if the TVP employs primary keys.
Can we have TVP column inside a TVP? I tried creating such item, getting ” A column cannot be of a user-defined table type.”
How to resolve this issue?
Very good article – I learnt this Tabled Parameter in first time, Thanks Lot – Reghuraja
how can i create function sql server with passing dynamic arguments.Pls explain
I am assuming 2 dynamic parameters.
declare the parameters with varchar datatype. inside the function based on the datatype change the assignment.
I am using This Feature in my Application… I have 2 Table Value Parameters in my Procedure. While Calling Procedure I will pass any one Value… How to make the table value parameter as not mandatory. Pls help to me for solve this…
I tried but it shows error message
Hi pinal,
Please show us how to pass table valued parameter across databases. Or clarify that if there is a way to do this.
Hi,
I have created a table type using CREATE TYPE.
Now I am trying to get a CREATE FUNCTION (returning a table) to return this type but so far have not been able to figure out if it is possible and what is the syntax. Can anyone help me? It would be supremely weird if I cannot use the definition of a table-type to determine a function output…
Thanks!
Andrew
CREATE TYPE TBL_TYPE AS
table (iCOL1 int not null,iCOL2 int not null)
go
CREATE FUNCTION [dbo].[fTest]
(@ai_param1 integer
)
RETURNS @blah TBL_TYPE
AS
BEGIN
INSERT @blah (iCOL1,iCOL2) VALUES (1,2);
RETURN @blah
END
Can some explain, How it is different from table varable?
HI Dave I tried so many times to create a user define table but same as above an error is occuring. Am I missing something?
Hi
We are using sql 2008 I have created stored procedure with user define table type parameter but on visual studio 2010 on server pane i can’t see those types and I cannot drag stored procedure on tho my dbml file.
Hola, como seria en vb6.0 mandar un struct a la parametro type table del store procedure?
Hi ,
I want to create a table With number of ristriction on the number of rows ie. I want to creat a table “Dept” with the ristriction of 10 Rows. we can only insert 10 records in table.
Regards,
Arpit
Once again, Great Job !
is it possible to use table-valued parameters as arguments to user defined functions?
For exemple, the type:
CREATE TYPE LDR_INT_ARRAY AS TABLE
(
Cod int not null
)
the function:
CREATE FUNCTION test
(
@year int,
@month int,
@number_evt LDR_INT_Array readonly
)
RETURNS TABLE
AS
RETURN
(
select top 10 *
from PFFINANC
where ANOCOMP = @year and MESCOMP = @month and NROPERIODO in (select Cod from @number_evt)
)
execution:
declare @lst as LDR_INT_Array
insert into @lst values (1)
insert into @lst values (2)
select * from @lst
select * from dbo.test (2012,12, @lst)
Error:
Msg 137, Level 16, State 1, Line 5
Must declare the scalar variable “@lst”.
but if i try with a stored procedure instead of a function it works.
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]