Table Value parameters (TVP) is a one of new features in SQL Server 2008. In SQL Server 2005 or earlier version, it is not possible to pass the table variable as parameter to a stored procedure. The common way to insert the rows one by one and another approach would be to create a stored procedure that takes long string variables and create table from them. Another approach would be to use as XML parameter and parse it inside the procedure. Following simple example helps to understand the Table value parameters concept and usages.
Steps involved in Creating and using table value parameters.
Ø Create Table type using user-defined data types.
Ø Create Store procedure or function that will accept table type as parameters.
Ø Create a table variable data and insert the data.
Ø Call the procedure or function and pass the table value parameter.
Table Type can be created using T-SQL or SQL Management Studio (SSMS)
USE TestDB
GO
CREATE TYPE ContactTable AS TABLE
(
ContactId INT NOT NULL,
ContactType VARCHAR(5) NOT NULL,
Value VARCHAR(100) NOT NULL
)
GO
Next Step to create a stored procedure that has the “ContactTable” data type as parameter
/*
Create a Store procedure
*/
CREATE PROC Usp_Insert_Contacts
@contact ContactTable READONLY
AS
INSERT INTO Contact (ContactId, ContactType, Value)
SELECT
ContactId,
ContactType,
Value
FROM
@contact
Go
Limitations to passing table parameters
Table-valued parameters must be passed as input READONLY parameters to stored procedure. DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter are not allowed in the body of a routine. You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement
Next step to declare a “ContactTable” variable and insert the sample data
DECLARE @Contact ContactTable
INSERT INTO @Contact(ContactId,ContactType,Value) VALUES(1,'Email','Test@xyz.com')
INSERT INTO @Contact(ContactId,ContactType,Value)
VALUES(1,'Fax','123-456-3454')
INSERT INTO @Contact(ContactId,ContactType,Value)
VALUES(1,'Phone','234-456-3456')
GO
Now pass the variable to the procedure
EXEC Usp_Insert_Contacts @Contact
GO
Calling the stored procedure through .NET application
SqlClient allows the application to populate the TVP from DataTable, DbDataReader or System.Collections.Generic.IList<SqlDataRecord> objects. Create a console application to call the store procedure using SqlClient library
Shared cs As String = "Data Source=<Server Name>; Initial Catalog=<DB Name>;Persist Security Info=True;User ID=<User>;Password=<Pwd>"
Shared Sub TestTVP()
Try
Using con As New SqlConnection(cs)
con.Open()
Dim dt As New DataTable("Contact")
dt.Columns.Add(New DataColumn("ContactId", GetType(Int32)))
dt.Columns.Add(New DataColumn("ContactType", GetType(String)))
dt.Columns.Add(New DataColumn("Value", GetType(String)))
dt.Rows.Add(New Object() {1, "Email", "'Test@xyz.com"})
dt.Rows.Add(New Object() {1, "Fax", "123-456-3454"})
dt.Rows.Add(New Object() {1, "Phone", "'234-456-3456"})
Dim param = New SqlParameter()
param.ParameterName = "@contact"
param.Value = dt
param.SqlDbType = SqlDbType.Structured
param.TypeName = "ContactTable"
Dim cmd = New SqlCommand("Usp_Insert_Contacts", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
con.Close()
End Using
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Sub
Following code uses the enterprise library to call the stored procedure with data table
public Sub TestTVPWithEnterpriseLibrary()
Dim db As Database = DatabaseFactory.CreateDatabase("Test")
Dim cmd As DbCommand = db.GetStoredProcCommand("Usp_Insert_Contacts")
Dim dt As New DataTable("Contact")
dt.Columns.Add(New DataColumn("ContactId", GetType(Int32)))
dt.Columns.Add(New DataColumn("ContactType", GetType(String)))
dt.Columns.Add(New DataColumn("Value", GetType(String)))
dt.Rows.Add(New Object() {1, "Email", "'Test@xyz.com"})
dt.Rows.Add(New Object() {1, "Fax", "123-456-3454"})
dt.Rows.Add(New Object() {1, "Phone", "'234-456-3456"})
cmd.Parameters.Add(New SqlParameter("@contact", dt) With {.SqlDbType = SqlDbType.Structured})
db.ExecuteNonQuery(cmd)
End Sub
The above part of the article is contributed by a friend of mine, Krishnan Balagurusamy. With his permission, I published it here. Thank you, Krishnan!
I further enhanced the article with following addition:
I further enhanced the article with following addition:
However, there is a bug in ADO.Net ( up to 4.0) related to TVP, which make it a bit troublesome to use TVP.
When using SqlCommandBuilder.DeriveParameters to establish parameters, The type name of TVP parameter is set to be 3 parts name <database Name>.<Schema Name> .<Type Name>. Take an example, User Defined Table Type of UDTT_KEYVALUE will be resolved into MyDB.dbo.UDTT_KEYVALUE
This will result in following error:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 3 ("@VOIDTABLE"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length database name specified. Database name is not allowed with a table-valued parameter, only schema name and type name are valid.
This bug is carried over to Database Access Application Block Database.DiscoverParameters() method.
To fix the problem, I add a method in DAABWrapper class as following:
C#:
protected DbCommand GetStoredProcCommand(Database database, string packageAndProcedureName)
{
DbCommand myCommand = _database.GetStoredProcCommand(packageAndProcedureName);
_database.DiscoverParameters(myCommand);
foreach ( SqlParameter Parameter in myCommand.Parameters)
{
if (Parameter.SqlDbType == SqlDbType.Structured)
{
string[] typeNameParts = Parameter.TypeName.Split(New Char[] {"."c});
if (typeNameParts.Length == 3)
{
Parameter.TypeName = string.Format("{0}.{1}", typeNameParts[1], typeNameParts[2]);
}
}
}
return myCommand;
}
VB.Net
Protected Function GetStoredProcCommand(ByVal procedureName As String) As DbCommand
Dim myCommand As DbCommand = _DataBase.GetStoredProcCommand(procedureName)
_DataBase.DiscoverParameters(myCommand)
For Each Parameter As SqlParameter In myCommand.Parameters
If Parameter.SqlDbType = SqlDbType.Structured Then
Dim typeNameParts As String() = Parameter.TypeName.Split(New Char() {"."c}) If typeNameParts.Length = 3 Then
Parameter.TypeName = String.Format("{0}.{1}", typeNameParts(1), typeNameParts(2))
End If
End If
Next
Return myCommand
End Function
With the solution above, we can simply use GetStoredProcCommand method to establish command object together with the parameters, including TVPs.
With the DAABWrapper class as its base class, the method above would be rewritten as following:
public Sub TestTVPWithEnterpriseLibrary()
Dim cmd As DbCommand = db.GetStoredProcCommand("Usp_Insert_Contacts")
Dim dt As New DataTable("Contact")
dt.Columns.Add(New DataColumn("ContactId", GetType(Int32)))
dt.Columns.Add(New DataColumn("ContactType", GetType(String)))
dt.Columns.Add(New DataColumn("Value", GetType(String)))
dt.Rows.Add(New Object() {1, "Email", "'Test@xyz.com"})
dt.Rows.Add(New Object() {1, "Fax", "123-456-3454"})
dt.Rows.Add(New Object() {1, "Phone", "'234-456-3456"})
cmd.Parameters. ("@contact").value = dt)
ExecuteNonQuery(cmd)
End Sub
Enjoy programming.
if you are intested to have the complete code file for the wrapper class, Please email me... I would be happy to email it to you...
Have you tried using Microsoft Enterprise libraries (DAAB) to create the command object and pass the user-defined type? I used DbTpe.Object and passed the datatable and it is failing with a RPC... error.
ReplyDeleteThis comment has been removed by the author.
DeletePlease refer to teh second part of the article. In it, I have explained what's wrong and how to fix it.
Delete