Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

Table-valued parameters in SQL Server 2008

Modified: 2008/08/26 15:55 by vanthoog - Categorized as: Csharp, SQL Server
A common problem in SQL Server 2005, and previous versions, when making stored procedures is the fact that you cannot define a parameter of type array or collection or something like that. This is for example a nuisance when you want to make a stored procedures for returning a number of records identified by a random number of id’s.

In SQL Server 2005, and previous versions, people sometimes solved this by defining a parameter as VARCHAR(MAX), supplying the id’s as a comma-separated string and writing some code to decode the comma-separated string of id’s into a temporary table containing the id’s.

In SQL Server 2008 a much more elegant solution is available, called table-valued parameters. The basic idea is to define a user-defined type of data type table and then use this user-defined type as a parameter in your stored procedure.

In this article we will give a small example of table-values parameters. In this example the following table is used:

SET NOCOUNT ON

CREATE TABLE TBVCompany
(
   ID INT NOT NULL PRIMARY KEY,
   Title VARCHAR(28) NULL
)
GO

INSERT INTO TBVCompany 
		(ID,Title)
     VALUES	(1,'Allround computer company'),
		(2,'Hardware division'),
		(3,'Disk factory'),
		(4,'Processor factory'),
		(5,'Software division'),
		(6,'OS''s'),
		(7,'Office tools')
GO

SELECT * FROM TBVCompany
GO

ID          Title
----------- ----------------------------
1           Allround computer company
2           Hardware division
3           Disk factory
4           Processor factory
5           Software division
6           OS's
7           Office tools

Step 1 is to define a user-defined type for storing a list of id’s (of data type int):

CREATE TYPE IntsType AS TABLE (ID INT NOT NULL)
GO

Step 2 is to define a stored procedure using this used-defined type for receiving a list of id’s. The parameter using the user-defined type must be marked as READONLY. In this example we will use a simple stored procedure for retrieving all company records with the specified id’s:

CREATE PROCEDURE GetTBVCompanies
(
	@IDs IntsType READONLY
)
AS
	SELECT * FROM TBVCompany 
	WHERE ID IN (SELECT ID FROM @IDs)
GO

The next code snippet calls this stored procedure in various ways:

DECLARE @ids IntsType;

-- Call the sp with an empty TBV parameter to show that this is not a problem.
EXEC GetTBVCompanies @ids;

-- Call the sp with a TBV parameter filled with some id's, of which one id (666) doesn't exist.
INSERT INTO @ids VALUES (2) , (4), (5), (666);
EXEC GetTBVCompanies @ids;

DELETE FROM @ids;

-- Call the sp with a TBV parameter filled with some id's, of which one id (3) appears twice.
INSERT INTO @ids VALUES (3) , (1), (3);
EXEC GetTBVCompanies @ids;

ID          Title
----------- ----------------------------

ID          Title
----------- ----------------------------
2           Hardware division
4           Processor factory
5           Software division

ID          Title
----------- ----------------------------
1           Allround computer company
3           Disk factory

Table-valued parameters can also be used in functions. Here is an example of a function (returning a concatenated string of company titles) using a table-valued parameter of type IntsType:

CREATE FUNCTION GetTBVCompanyTitles
(
	@IDs IntsType READONLY
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

	DECLARE @tempString NVARCHAR(MAX) = ''
	
	SELECT @tempString += Title + ' ' FROM TBVCompany 
	WHERE ID IN (SELECT ID FROM @IDs)
	
	RETURN @tempString
END

The next code snippet calls this function in various ways:

DECLARE @ids IntsType;

-- Call the function with an empty TBV parameter to show that this is not a problem.
SELECT dbo.GetTBVCompanyTitles(@ids) as 'Company titles';

-- Call the function with a TBV parameter filled with some id's, of which one id (666) doesn't exist.
INSERT INTO @ids VALUES (2) , (4), (5), (666);
SELECT dbo.GetTBVCompanyTitles(@ids) as 'Company titles';

DELETE FROM @ids;

-- Call the function with a TBV parameter filled with some id's, of which one id (3) appears twice.
INSERT INTO @ids VALUES (3) , (1), (3), (666);
SELECT dbo.GetTBVCompanyTitles(@ids) as 'Company titles';

Company titles
-----------------------------------------------------------------------


Company titles
-----------------------------------------------------------------------
Hardware division Processor factory Software division 

Company titles
-----------------------------------------------------------------------
Allround computer company Disk factory


So how do use table-valued parameters in your C# code? Or in other words, how can we call the stored procedure GetTBVCompanies from our C# code? Well, in .NET 3.5 the easiest way, in theory, would be to use LINQ. However, if you drag the stored procedure GetTBVCompanies onto your database markup language metadata file (dbml file) and then compile your code, you will get the following error message:

DBML1005: Mapping between DbType 'Structured' and Type 'System.Object' in Parameter 'IDs' of Function 'dbo.GetTBVCompanies' is not supported.

Apparently table-valued parameters are not supported in LINQ! The only way to use table-valued parameters is by using “old-school” ADO.NET. The following code snippet shows a console application which calls the stored procedure GetTBVCompanies using 4 ID’s as input and displays its output:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ShowSomeCompanies
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a data table to serve as temporary storage for the list of ID's and provide its structure.
            DataTable idsDataTable = new DataTable();
            idsDataTable.Columns.Add("ID", typeof(int));

            // Fill this data table with some ID's.
            idsDataTable.Rows.Add(1);
            idsDataTable.Rows.Add(666); // This ID does not exist in the database.
            idsDataTable.Rows.Add(5);
            idsDataTable.Rows.Add(3);

            // Specify a connection to the database.
            using (SqlConnection sqlConn = new SqlConnection(@"server=myserver;database=mydb;Trusted_Connection=no;User ID=myuser;Password=mypw;"))
            {
                // Define a command object for calling the stored procedure.
                SqlCommand sqlCmd = sqlConn.CreateCommand();
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "GetTBVCompanies";

                // Add the parameter @IDs and supply the data table as input.
                SqlParameter param = sqlCmd.Parameters.AddWithValue("@IDs", idsDataTable);

                sqlConn.Open();

                // Call the stored procedure and retrieve the output.
                System.Data.SqlClient.SqlDataReader sqlReader = sqlCmd.ExecuteReader();

                if (sqlReader.HasRows)
                {
                    while (sqlReader.Read())
                    {
                        Console.WriteLine("ID: {0} - Title: {1}", sqlReader"ID", sqlReader"Title");
                    }
                }
                else
                {
                    Console.WriteLine("No records found.");
                }

                sqlReader.Close();
            }
        }
    }
}

This small program produces the following output:

ID: 1 - Title: Allround computer company
ID: 3 - Title: Disk factory
ID: 5 - Title: Software division

 © Evident Interactive BV