Yesterday I showed how there are some new language constructs in TSQL that will be very familiar to C# developers. I will be doing three talks on TSQL next week at TechED Developers in Barcelona and the talks are designed for application developers. (But database geeks will enjoy these sessions as well!) If you write any TSQL code in your applications today, or interact with it, these sessions will give you some advice and help change the way you approach particular problems.
Today I want to show a new feature about using Table Valued Parameters (TVP) in a stored procedure and then calling it from an ASP .NET page or any other .NET client using ADO.NET. A TVP is new in SQL Server 2008, so you can now declare a type of TABLE, insert data into it, and then pass it to a stored procedure. Have you ever passed in a CSV string to a DAL or stored procedure or even an XML data type, then parsed it and executed some SQL? TVP will eliminate that. Here is an example:
First create a new table for us to play with:
Create Table TVP_TEST
(Field_ID int primary key, Field_DESC varchar(10))
Next create the TVP by declaring a new type. Notice that I have created the type to be exactly the same shape as the underlying table, this is not a requirement, however, it is important to do so if you are going to be doing inserts as we do below.
CREATE TYPE TVP_TESTType AS TABLE( Field_ID int,
Field_DESC varchar(10))
Next create a variable as type TVP_TESTType and insert some data into it, insert is done the same way we would add data to a normal table. Notice that this only puts data into the variable, NOT the underlying table:
--put data into the type
DECLARE @MyTable as TVP_TESTType
--put data into the type
INSERT INTO @MyTable VALUES
(1, 'Test'), (2, 'Next Test')
Now you can insert data into a table very easily (remember @MyTable has to be in scope):
INSERT INTO TVP_TEST
SELECT * FROM @Mytable
To get really useful, let’s create a stored procedure that will accept as a parameter the TVP_TESTType data type and insert data into the TVP_TEST table using the parameter, notice no parsing of CSV strings, etc:
CREATE PROCEDURE usp_ins_MyTable
(@MyTVP as TVP_TESTType READONLY)
As
INSERT INTO TVP_TEST
SELECT * FROM @MyTVP
You can call this stored procedure as so, as long as @MyTable is in scope and is filled with data:
exec usp_ins_MyTable @MyTVP=@MyTable
But what is really exciting is calling this data from a .NET client. If you have an ADO .NET dataset that is the same shape of the TVP you can pass it in from the client to the server, greatly enhancing data access and batch operations.You can pass in a dataset as a parameter using the SqlDbType.Structured as its data type.
Here is some code from C#, just remember that ds is our active dataset filled with data and shaped the same way as TVP_TESTType:
SqlCommand cmd = new SqlCommand("usp_ins_Portfolio", conn);
cmd.CommandType = CommandType.StoredProcedure;
//add the ds here as a tvp
SqlParameter sp = cmd.Parameters.AddWithValue("@Portfolio", ds.Tables[0]);
//notice structured
sp.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
Pretty easy! Now you can pass data sets in as parameters to a stored procedure, eliminating some funky parsing on either the client or server.