Table variables have been around since SQL Server 2000, but in SQL Server 2008 you can use them as parameters. No more comma-separated strings, parsing with functions, staging tables, custom CLRs, tricky uses of the file system, or bulk inserts. Combine table variables with the power of SQL Server Integration Services (SSIS) and you can implement an inserting task that reduces the need for row-by-row processing, simplifies code for maintainability, and keeps trips to the database at a minimum. In this article, I'll show you how to create a stored procedure that takes a table variable as a parameter and an SSIS package that implements it.

Related: SSIS Package Pings Servers

Set Up a Table Variable Parameter


First, run the code in Listing 1 to create the table.

Listing 1: DDL Statement to Create PersonTest Table
 
set nocount on
use tempdb
if object_id('dbo.persontest','u') is not null drop table dbo.persontest
CREATE TABLE \\[dbo\\].\\[PersonTest\\](
    \\[PersonFirstName\\] \\[varchar\\](50) NULL,
    \\[PersonLastName\\] \\[varchar\\](50) NULL
)

PersonTest is a simple table with first and last names. The code in this article places all objects in tempdb.

The table variable parameter depends on a user-defined table type that's stored in the system tables and displayed in the programmability hierarchy of SQL Server Management Studio (SSMS). Listing 2 shows the code to create your table type, called PERSONTYPE.

Listing 2: DDL Statement to Create User Defined Person Type
 
IF EXISTS (SELECT * FROM sys.types st
  JOIN sys.schemas ss ON st.schema_id = ss.schema_id
  WHERE st.name = N'PersonType' AND ss.name = N'dbo')
DROP TYPE \\[dbo\\].\\[PersonType\\]
 
CREATE TYPE \\[dbo\\].\\[PersonType\\] AS TABLE(
  \\[personfirstname\\] \\[varchar\\](50) NULL,
  \\[personlastname\\] \\[varchar\\](50) NULL
)

 

This type will be the structure for your table variable. One limitation of user-defined types is that they maintain a relationship with the items that use them. If you need to make modifications to this type later, you'll need to remove the reference in your stored procedure. See the sidebar at the end of this article for more limitations.

Once created, you can view your object in SSMS under Programmability\Types\User Defined Table Types, but you won't be able to modify the structure from the SSMS GUI. You need to use T-SQL to drop and recreate this object.

Create Stored Procedure to Take the New Table Type


Listing 3 gives the T-SQL code to create a stored procedure to accept this type as a parameter.

Listing 3: DDL Statement to Create Stored Procedure with @table parameter
 
IF EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'\\[dbo\\].\\[insertPersons\\]')
  AND type in (N'P', N'PC'))
DROP PROCEDURE \\[dbo\\].\\[insertPersons\\]
GO
 
CREATE PROCEDURE \\[dbo\\].\\[insertPersons\\]
(@Persons PersonType READONLY )
AS
BEGIN
SET NOCOUNT ON;
insert into PersonTest
select * from @Persons
END
GO

Note that your parameter is read only, although this limitation might be lifted in future releases. Test your stored procedure with the following code:

Declare @mytable PersonType
    insert into @mytable select 'firstnametest', 'lastnametest'
    execute insertpersons @mytable

Also while testing, try to drop your new table type.

DROP TYPE \\[dbo\\].\\[PersonType\\]

Note that you'll have to alter your stored procedure if you want to drop your table type. If you try the above drop statement, you'll get the following error:

Msg 3732, Level 16, State 1, Line 2 Cannot drop type 'dbo.PersonType' because it is being referenced by object 'insertPersons'. There may be other objects that reference this type.

Create an SSIS Package


Open Visual Studio and create a new blank Integration Services Project from the Business Intelligence Project type. In the Control Flow tab

Right-click the Connections Manager section of the Control Flow tab and add a new ADO.NET connection to the AdventureWorks database. If you don't have AdventureWorks, any source with a table containing first and last names will do, assuming the data types match and you use the same data provider for both connections.

  1. Add another ADO.NET connection, but this time to the tempdb database. Rename it sqlTempDbDestination.
  2. Right-click the Control Flow Designer and select variables. Add a variable named rsPersonTest with a data type of Object.
  3. Drag a Script task to the Control Flow Designer.
  4. Drag a Data Flow task to the Control Flow Designer. Double-click this object to get into the Data Flow tab.

 

In the Data Flow tab

  1. Drag an ADO.NET source to your work area. Open this object and set the table to the Person.Contact table, or whatever source you choose that has first name and last name columns.
  2. Drag a Recordset Destination to your work area and connect to it from your source.
  3. In Recordset Destination properties, input the firstname and lastname columns. Then, in the Component Custom properties, add rsPersonTest (User::rsPersonTest) to the variables. This will store your record set into the rsPersonTest variable that you created earlier.

In the Control Flow tab

  1. Make sure you connect your Dataflow task to your Script task.
  2. Access your script component properties.
  3. Set the ReadOnlyVariables property to the variable you created before (User::rsPersonTest). This lets you access this variable within the component.
  4. Set the ScriptLanguage to Microsoft Visual Basic 2008.
  5. Click Edit Script immediately after step 4. Otherwise, the script language will be reset to C#.
  6. Note that in the VSTA environment there isn't a need to save anything. Close the window by hitting the X button and it will save all your changes.
  7. Also note that you can stop or debug your code in the Script task (unlike the Script Component object available in the Control Flow tab).

Now use the following code to import.

        Imports System.Data.OleDb
        Imports System.Data.SqlClient

That's it. Now the easy part. Add your code to the main sub using the code in Listing 4. Note that you're using the AddwithValue method, which takes a string (the parameter) and an object (which lets you use your datatable).

Listing 4: Adding code to the Main Sub
Dim oleDA As New OleDbDataAdapter
Dim dt As New DataTable
oleDA.Fill(dt, Dts.Variables("rsPersonTest").Value)
Dim sqlConn As SqlConnection
Dim sqlCmd As New SqlCommand
sqlConn = CType(Dts.Connections("sqlTempDbDestination").AcquireConnection(Dts.Transaction), SqlConnection)

sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.CommandText = "insertPersons"

sqlCmd.Parameters.AddWithValue("@Persons", dt)
If sqlConn.State = ConnectionState.Closed Then sqlConn.Open()
sqlCmd.Connection = sqlConn
sqlCmd.ExecuteNonQuery()
If sqlConn.State = ConnectionState.Open Then sqlConn.Close()
Dts.TaskResult = ScriptResults.Success
Close out of the Script Task and run your package with F5. If you get an error, you can always review the Progress tab or set a break point in the script task code to find out what went wrong. (When I first ran mine, I received an error because I'd forgotten to rename my destination connection properly.) When everything turns green in your package, it will have migrated your list of names from AdventureWorks to your Tempdb.PersonTest table utilizing your insertPersons procedure, including your precious new @persons table variable parameter.

Code Maintainability


There are many different paths that you can choose to move data around, but in my book, code maintainability is just as important as performance. Keeping the majority of your code in one place and making it easy to fix later keeps the office sane.

The entire reason I jumped into using table variable parameters was that I wanted to use the new Merge function with SQL Server 2008. Using this method, I could feed an entire set of data to my procedure instead of making a call for every record. While SSIS performs well with row-by-row processing, sometimes it just isn't necessary. This simple method for using table variable parameters with SSIS shows how you can simplify code for maintainability, reduce the number of hits on the database, and keep all your data manipulation tasks in one place—the stored procedure, where you and I are most comfortable.

Sidebar: Limitations of Using the Table Type as a Parameter

  • It's read-only.
  • You can't drop or alter it once it's referenced (you need to remove references first).
  • You can't issue an alter command, you can only drop and recreate.
  • You can't pass it to user-defined functions.
  • No stats are maintained.
  • It's stored in tempdb and is an in-memory object, which always needs to be considered when working with large amounts of data.

Related Reading: