Tuesday, April 22, 2008

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

10 comments:

Glyn Radcliffe-Brine said...

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?

Plamen Ratchev said...

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.

Glyn Radcliffe-Brine said...

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.

Anonymous said...

Thanks for your posting, It helped me ;)

Yamil said...

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

Plamen Ratchev said...

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.

Yamil said...

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!

Anonymous said...

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?

Plamen Ratchev said...

You can use scalar output parameters, only the TVP cannot be output. Check the syntax of your parameter declaration, something must be incorrect.

Armando Iswahyudi said...

Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.

http://armandomanabu.blogspot.com armando manabu
http://manabumikami.blogspot.com manabu mikami
http://tkrdua.wordpress.com tkr dua
http://mandomanabu.wordpress.com mando manabu