Prior to SQL Server 2005, you only had a few labor-intensive options if you wanted to order to pass a collection of values to a stored procedure from your .NET application.

If your data was an array of objects containing only single values, such as an array of strings, you could pass a delimited string to the stored procedure. Inside your procedure you would have to then parse the string and fill a temporary table with each piece of data.

If you had an array of objects containing multiple data points, you would have to fill some temporary table in the database and then reference the table in your stored procedure.

Thankfully, SQL Server 2008 has a cool new Table-Value Parameters data type. This allows you to pass objects like .NET DataTables as parameters to stored procedures!

/* Create a table type. */
CREATE TYPE StateNameType AS TABLE(StateName VARCHAR(50));
GO

/* Create a procedure to receive data for the table-value parameter.*/
CREATE PROCEDURE uspStateInsert (
@NewStates StateNameType ) AS
SET NOCOUNT ON;

INSERT INTO State(Name)
SELECT *
FROM @NewStates;
GO

You can do something similar in SQL Server 2005 without having to use a temporary table. Let’s say that you have an array of states in your Visual Basic .NET application, like this:

Dim arrState() As String = {"Alabama", "Alaska", "Arizona", "Arkansas"}

You could use the following code to serialize it to an XML string:

'Serialize to a memorystream object
Dim ser As New XmlSerializer(arrState.GetType)
Dim mem As New MemoryStream
ser.Serialize(mem, arrState)

'Get the data out of the memorystream object and into a string
mem.Flush()
mem.Position = 0
Dim sr As New StreamReader(mem)
Dim strStates As String = sr.ReadToEnd()


The resulting string would look like this:

<?xml version="1.0" ?>
<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<string>Alabama</string>
<string>Alaska</string>
<string>Arizona</string>
<string>Arkansas</string>
</ArrayOfString>

You could then set up a stored procedure in SQL Server 2005 that received an XML parameter and returned the XML string as a table:

CREATE PROCEDURE State_List (
XML
) AS
BEGIN

SELECT tblState.StateName.value('.', 'VARCHAR(50)') AS StateName
FROM @States.nodes('/ArrayOfString/string') AS  tblState(StateName)

END
GO


The resulting output is:

• StateName
• Alabama
• Alaska
• Arizona
• Arkansas

If you wanted to use the data passed to the stored procedure as a filter, you can even join on the XML table, like this:

CREATE PROCEDURE Vendor_Search (
@States XML
) AS
BEGIN

SELECT Vendor.VendorName, Vendor.StateName
FROM Vendor INNER JOIN
@States.nodes('/ArrayOfString/string') AS tblState(StateName) ON  tblVendor.St = tblState.StateName.value('.', 'VARCHAR(50)')

END


No temporary tables needed! Very cool!