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
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 https://www.microsoft.com/en-us/download/details.aspx?id=51958 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
I have the same problem, does anybody have a solution yet?
My guess was the following and according to the syntax highlighting the function declaration could be valid, but when I try to return the value I get an error ‘Must declare the scalar variable “@blah”.’
CREATE FUNCTION [dbo].[fTest] (@ai_param1 integer)
RETURNS TBL_TYPE
AS
BEGIN
DECLARE @blah AS TBL_TYPE;
INSERT INTO @blah (iCOL1,iCOL2) VALUES (1,2);
RETURN @blah; — Must declare the scalar variable “@blah”.
END
Any ideas? The server is SQL-Server 2008-R2.
I now use the following work-around:
(sorry I just neutralized my productional code and didn’t take the example above because of lake of time)
CREATE TYPE [dbo].[IntList] AS TABLE([IntValue] [int] NOT NULL)
GO
CREATE FUNCTION [dbo].[SomeFunction](@ID_List IntList READONLY) — use User-Defined Table Type as input
RETURNS @ResultTable TABLE (Group_ID INT NOT NULL) WITH EXECUTE AS CALLER — use traditional table as output
AS
BEGIN
INSERT @ResultTable SELECT /* enter query here, eg. SELECT XXX FROM YYY WHERE YYY.ZZZ_ID IN (SELECT * FROM @ID_List); */ 1;
RETURN
END
GO
Can some explain, How it is different from table varable?
A table variable cannot be passed as a parameter to other objects
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
You can create an INSERT trigger that will rollback all rows exceeding 10 rows
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.