Table Valued Parameters

Passing multiple rows of data to stored procedures has always intrigued application developers. Many different methods have been used, more evolving around XML or some form of concatenated list. Those approaches require a method for parsing before the data can be used in normalized table format.

SQL Server 2008 adds another option: table valued parameters. This allows sending a table as parameter to the stored procedure. The current implementation still has shortcomings because it is a read-only parameter, and it cannot be used as output parameter.

Here is one example to demonstrate using table valued parameters to pass a table as stored procedure input.


 loan_nbr INT PRIMARY KEY,

 loan_date DATETIME,

 loan_amount DECIMAL(15, 2));


-- User-defined table type



 loan_nbr INT PRIMARY KEY,

 loan_date DATETIME,

 loan_amount DECIMAL(15, 2));




-- Procedure with table valued parameter

-- Must use the READONLY clause


 @Loans LoanTable READONLY




 SELECT loan_nbr, loan_date, loan_amount

 FROM @Loans;




-- Declare table variable of the new type

DECLARE @NewLoans LoanTable;


-- Initialize the table variable with data


VALUES (1, '20080101', 10000.00),

       (2, '20080203', 15000.00),

       (3, '20080315', 25000.00);



-- Insert new loans using the table variable as parameter

EXEC InsertLoans @Loans = @NewLoans;

The really cool part here is that table valued parameters are fully supported on the client side via ADO.NET (.NET Framework 3.5 or later) . That means we could easily have a data bound control like grid on the client side that can be used to feed a table parameter to update database table via a stored procedure. Here is a look at what that client code will be.

DataTable loans;


loans = new DataTable();

loans.Columns.Add("loan_nbr", typeof(int));

loans.Columns.Add("loan_date", typeof(System.DateTime));

loans.Columns.Add("loan_amount", typeof(decimal));


using(SqlCommand cmd = new SqlCommand("InsertLoans", conn)


  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.AddWithValue("Loans", loans);




Table-Valued Parameters

9 replies
  1. Glyn Radcliffe-Brine
    Glyn Radcliffe-Brine says:

    Hi Plamen,

    Many thanks for the example. I have a strange problem with it – My 2008 Server code (running on Windows Server 2008 with .NET 3.5 SP1) allows me to create the table data type but complains that the table data type is invalid when I try to use it in the procedure declaration.

    Any ideas?

  2. Plamen Ratchev
    Plamen Ratchev says:

    Hi Glyn,

    What is the code you use to create the stored procedure and the table type? What error do you get? One thing to watch for is that you need to specify the READONLY option for the table valued parameter in the stored procedure definition, or you will get an error.

  3. Glyn Radcliffe-Brine
    Glyn Radcliffe-Brine says:

    Hi Plamen,

    Here’s the strangest thing: My code is an exact copy of yours. I’ve just logged on to my server to copy it and noticed that there were no red squiggly lines any more. I ran the code without any changes whatever, and it worked perfectly!

    Sorry to have troubled you – Happy now but somewhat puzzled at what the problem could have been.

  4. Yamil
    Yamil says:

    This is just what I was looking for….Thanks for the post. I am having the same issue that was mentioned by Glyn. After I create the User-Defined Type it shows up under Types in SQL server. When I create the Store Procedure and declare the parameter with the User-Defined Type it says "Invalid Data Type" with squiggly lines under it. I did use ReadOnly when declaring. Any Ideas would be most helpful.

    Thanks again.

    create procedure spProductInsert
    @ProductTableType myTableType readonly
    into Customers (Name, City, Phone)
    select Name, City, Phone
    from @CustomersTable;

  5. Plamen Ratchev
    Plamen Ratchev says:

    Hi Yamil,

    The squiggly lines do not really indicate an issue. Most likely the IntelliSense cache is not refreshed. Click Ctrl+Shift+R or from the Edit menu select IntelliSense and then Refresh Local Cache.

  6. Yamil
    Yamil says:

    Plamen, I can't thank you enough. I did as you mentioned and the lines went away and the Store Procedure Executed. Thank you for the quick response too.

    Again, great post!

  7. Anonymous
    Anonymous says:

    Refreshing the Intellisense cache was nice. However, if I include any other parameters to the stored proc, specifically two scalar output parameters, I get the error "Must declare the scalar variable SampleTVP" on the TVP when I try to alter the proc. Are we not allowed to include output parameters in such a stored proc?


Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *