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.
CREATE TABLE Loans (
loan_nbr INT PRIMARY KEY,
loan_date DATETIME,
loan_amount DECIMAL(15, 2));
-- User-defined table type
CREATE TYPE LoanTable
AS TABLE (
loan_nbr INT PRIMARY KEY,
loan_date DATETIME,
loan_amount DECIMAL(15, 2));
GO
-- Procedure with table valued parameter
-- Must use the READONLY clause
CREATE PROCEDURE InsertLoans
@Loans LoanTable READONLY
AS
INSERT INTO Loans
SELECT loan_nbr, loan_date, loan_amount
FROM @Loans;
GO
-- Declare table variable of the new type
DECLARE @NewLoans LoanTable;
-- Initialize the table variable with data
INSERT INTO @NewLoans
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);
cmd.ExecuteNonQuery();
}
Resources:
Table-Valued Parameters
http://msdn2.microsoft.com/en-us/library/bb510489(SQL.100).aspx
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?
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.
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.
Thanks for your posting, It helped me 😉
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
)
as
begin
insert
into Customers (Name, City, Phone)
select Name, City, Phone
from @CustomersTable;
end
go
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.
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!
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?
You can use scalar output parameters, only the TVP cannot be output. Check the syntax of your parameter declaration, something must be incorrect.