SQL SERVER – 2008 – Introduction to Table-Valued Parameters with Example

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:

  1. Create a table type and define the table structure
  2. Declare a stored procedure that has a parameter of table type.
  3. Declare a table type variable and reference the table type.
  4. Using the INSERT statement and occupy the variable.
  5. 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)

About these ads

78 thoughts on “SQL SERVER – 2008 – Introduction to Table-Valued Parameters with Example

  1. 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?

  2. 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?

  3. 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 “.

  4. 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

  5. hello…
    i want an answer for the following question:

    Wat query should we execute to just display first five records from a table?

  6. 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.

  7. 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

  8. 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)

  9. 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?

  10. 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.

  11. 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.

  12. 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

  13. 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

  14. 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

  15. 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]

  16. 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.

  17. 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.

  18. 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

  19. 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.

  20. 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?

    • I am assuming 2 dynamic parameters.
      declare the parameters with varchar datatype. inside the function based on the datatype change the assignment.

  21. 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…

  22. Hi pinal,
    Please show us how to pass table valued parameter across databases. Or clarify that if there is a way to do this.

  23. 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

  24. 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.

  25. 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

  26. 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.

  27. 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.

  28. 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

  29. 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.

  30. 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

  31. 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 ?

  32. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

  33. 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

  34. Thanks pinal,can i use normal parameters like varchar,int,datetime etc.. with table value parameter. if it is so example please

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s