Programmers have been faced to situations where they have data in a datatable in memory that needs to be passed to a stored procedure. But for a long time, there was no free lunch for developers! Workarounds needed to be found.

Those workarounds include (but not limit too) calling the stored procedure multiple time (one for each row of the datatable), creating a XML string to pass to the stored procedure which then needed to be parsed before being processed, or worst, creating a big string by concatenating all the values and then parse them in the stored procedures. You have been there, you know what I mean. Workarounds are not always efficient but it works.

If you were able to migrate to SQL Server 2008 or better (better is R2 for now), you can benefit from the Table Value Parameters (aka TVP). TVPs are not a workaround. They are really well implemented into SQL Server and .Net and they integrate in a very decent way. Even if they may look exactly as plain old tables on the first look, they are not so we need to deal with the small differences.

Demo code and other requirements

This month, the demo code is provided in both VB and C#. It was created using Visual Studio 2008. This feature was added with the .Net framework 3.5.

I have also provided a script to generate all the SQL objects you need (a new database, a new table, a new User-Defined Table type, a new Stored Procedure).

Another requirement to be able to test this month demo is to have access to a SQL Server 2008 (or better) instance. One good news is that SQL Server Express also supports this feature.

The SQL Side

The first thing you need to know about TVPs, is that they are strongly typed. What this means is that the tables that will be passed between stored procedures or between the code and the stored procedures need to be declared.

As I like to keep demo code sample, we will create a new database solely for the purpose of testing. I will do it using scripts because it is a lot easier but everything we do here can be done using the editor of SSMS.

To create the database, run these statements from SSMS (SQL Server Management Studio):

CREATE DATABASE DemoTVP
go
USE DemoTVP
go

Now that we have a test database, we need to create a table. This table can be scripted with this statement:

CREATE TABLE Person(
	ID			INT	NOT NULL IDENTITY(1,1) 
	, FirstName	VARCHAR(50) NULL 
	, LastName	VARCHAR(50) NULL 
	, FullName  AS (ISNULL(FirstName+' ','')+ISNULL(LastName,''))
	, PRIMARY KEY CLUSTERED (ID)
)

You recognized this simple table? I am sure you have many like this. It is a simple table with an identity column, 2 varchar columns (FirstName and LastName) that we will fill and a computed column to return the FullName.

From this table, we can create a User-Defined Table Type with this script:

CREATE TYPE PersonType AS TABLE (
	FirstName	VARCHAR(50) NULL 
	, LastName	VARCHAR(50) NULL 
	, FullName  AS (ISNULL(FirstName+' ','')+ISNULL(LastName,''))
)

There is not much difference. We need to use the “Create Type” syntax and almost everything else is the same. We can use identity columns, computed columns, primary keys, … Some of the stuff you cannot use includes check constraints. One other big limitation is that you cannot alter a type. You need to drop it and recreate it (much like regular Used Defined Data Types). You will find some other limitations later in this article.

Figure 1: Our demo database from SSMS

You might be tempted to try to insert directly into the new table type but you cannot. The analogy is simple: You need to see this type as a class for which you need to create an instance before using it.

This is how we do it:

DECLARE @person PersonType
INSERT INTO @person (FirstName, LastName) 
VALUES 
('Joe', 'Dalton')
, ('Jack', 'Dalton')
, ('Willam', 'Dalton')
, ('Averel', 'Dalton')
SELECT * FROM @person

So here we do 3 things:

• Declare a variable of the type we created
• Insert into that variable
• Select the variable to output its content

Figure 2: The result of the Select statement

One thing I don’t like about these type when debugging is that if you highlight the declare line and execute it, you cannot then highlight the Select statement and execute it. The scope of the variable is of a very short span, it only exist will it is running.

Now that we know how to we saw the basics of how it works, let’s implement it for real in a stored procedure.

This is the code required to create the stored procedure that receives a UDTT in parameter:

CREATE PROCEDURE PersonInsert
  @InputTable PersonType READONLY
AS 
BEGIN
	INSERT INTO Person ( FirstName, LastName )
	SELECT FirstName, LastName FROM @InputTable
END

Notice that the input table is read-only. This is a requirement. At the moment, it is the only option you have. TVPs are read-only (this is a limitation) and you cannot use any DML operations (Data Manipulation Language) like Insert, Update, and Delete. Because of this keyword, something tells me that it might change in a future (but I absolutely don’t have any cue here).

If you ever need to manipulate this data, you will need to first duplicate it into a temporary table.

Now that we have all the parts in place, let’s try a complete sample.

DECLARE @person PersonType
INSERT INTO @person (FirstName, LastName) 
VALUES 
('Joe', 'Dalton')
, ('Jack', 'Dalton')
, ('Willam', 'Dalton')
, ('Averel', 'Dalton')

EXEC PersonInsert @InputTable = @person

SELECT * FROM Person

If you run it twice, you will see that the rows are inserted twice.

Not only Stored Procedures can benefit from this new kind of parameters. User-Defined Functions (aka UDF) can too benefit from them (even if some MSDN pages are saying that it doesn’t work).

Check this script which creates a UDF which only return the number of rows received in a UDTT parameter:

CREATE FUNCTION dbo.testUDF(@InputTable PersonType READONLY)
RETURNS int
AS
BEGIN
	RETURN (SELECT COUNT(*) FROM @InputTable)
END

You have again the same limitation as in Stored Procedure but it will work. And to test it, you can use this snippet:

DECLARE @person PersonType
INSERT INTO @person (FirstName, LastName) 
VALUES 
('Joe', 'Dalton')
, ('Jack', 'Dalton')
, ('Willam', 'Dalton')
, ('Averel', 'Dalton')
SELECT dbo.testUDF(@person)

Even if we were to stop here, it would have been great. But wait, the best is yet to come (as least from the developer’s standpoint).

The Application side

Now that the SQL side is done and tested, the other thing I want to show you is how to use it from your .Net applications.

Figure 3: The demo application in action

While I was testing, I found another limitation. This limitation explains why I have 2 buttons on my form. The first button demonstrates the limitation while the second button (SHORT) shows a workaround.

In order to reduce the amount of code required, both buttons are calling the same method which has a Boolean parameter indicating if we are running the full version or the short version.

This method has the following code:

Private Sub TestTVPStoredProc(ByVal pShortVersion As Boolean)
    'Create a table
    Dim dt As New DataTable("Person")
    With dt
        .Columns.Add("FirstName", GetType(String))
        .Columns.Add("LastName", GetType(String))
        If Not pShortVersion Then
            .Columns.Add("FullName", GetType(String), "FirstName + ' ' + LastName")
        End If

        'Add rows
        .LoadDataRow(New Object() {"Joe", "Dalton"}, True)
        .LoadDataRow(New Object() {"Jack", "Dalton"}, True)
        .LoadDataRow(New Object() {"Willam", "Dalton"}, True)
        .LoadDataRow(New Object() {"Averel", "Dalton"}, True)
    End With

    'establishing connection. 
    'you may have to customize the connection string to fit your situation
    Dim cn As New SqlConnection("Data Source=(local); Integrated security=SSPI; Initial Catalog=DemoTVP;")

    Try
        cn.Open()
    Catch ex As Exception
        MessageBox.Show("Unable to establish a connection to the database. Please check your connection string." + _
                        Environment.NewLine + Environment.NewLine + _
                        ex.Message, _
                        "Exception", _
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    End Try

    Try
        Dim cmd As SqlCommand = Nothing
        If pShortVersion Then
            cmd = New SqlCommand("PersonShortInsert", cn)
        Else
            cmd = New SqlCommand("PersonInsert", cn)
        End If
        cmd.CommandType = CommandType.StoredProcedure

        Dim param As SqlParameter = cmd.Parameters.Add("@InputTable", SqlDbType.Structured)
        param.Value = dt

        cmd.ExecuteNonQuery()

    Catch ex As Exception
        MessageBox.Show("Exception occured while calling the stored procedure." + _
                        Environment.NewLine + Environment.NewLine + _
                        ex.Message, _
                        "Exception", _
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    End Try

    'Close the connection
    cn.Close()
    cn.Dispose()
    cn = Nothing

    MessageBox.Show("Everything went well. You should have data into the database!", _
                    "Success", _
                    MessageBoxButtons.OK, _
                    MessageBoxIcon.Information)
End Sub

The code is self-explanatory. It first create a table like we did in SSMS with the 3 columns (forget the short version for now) and loads the table with data. A connection to the SQL server is then open. Finally, a command to call the stored procedure is created.

The only real thing to notice here is the type of parameter. You may be used to Int, VarChar, DateTime, … but for a TVP, we have to use the SqlDbType.Structured type (and this is also why you need the .Net Framework 3.5 because it wasn’t available before).

Now if you run the application and push the button, an exception will be thrown.

Figure 4: What?

Sincerely, I wasn’t expecting that problem. We tested the code directly into SSMS and it was working correctly. The problem comes from the fact that we have a computed column but you would get exactly the same problem with an identity column.

This why I have created a Short set (a new PersonTypeShort type and a new PersonShortInsert stored procedure) in the database. My code simply bypasses the computed column and calls the second stored procedure when the parameter has a True value. This is the easiest workaround you can get.

Another workaround exists. This workaround requires the usage of a List(Of SqlDataRecord) as shown into the “Final Remarks” section of Erland Sommarskog article (see the suggested reading).

Suggested reading

If you want to read more on that topic, I suggest that you take a look at:

• Table-Valued Parameters (Database Engine)
• Table-Valued Parameters in SQL Server 2008 (ADO.NET)
• Erland Sommarskog - Arrays and Lists in SQL Server 2008 - Using Table-Valued Parameters

Conclusion

I would have needed this feature many times in the last few years. One thing for sure, I now know it exists and I will use it the next time I need to provide a table of data to SQL Server.

It has a couple of limitations but the benefits are overwhelming.