One of the cool new features of SQL Server 2008 is using a table variable as a parameter in a stored procedure, which means that a C# or VB.NET program can build a data table and then pass it to the stored procedure as a parameter! A frequently-used alternative technique in earlier versions of SQL Server was to build a comma-separated list in the program, pass it to the started procedure via a VARCHAR parameter, and then parse the list in the procedure with the results added a row at a time into a temporary table or table variable.

I plan to write a detailed article about this great new feature, but I thought I would post a solution to a strange problem that I encountered during my first attempt at using a table as a stored procedure parameter.

I have been working on a search engine for a database of articles from a publication. The application searches on article title as well as keywords that are attached to the article or to photos. A list of checkboxes for each keyword type allows the user to select the keyword types that will be included in the search.

Here is a snippet of the C# code that calls the procedure. There is nothing new or exciting about this code except that SqlDbType.Structured must be specified as the parameter type on the SqlParameter.

... snip
DataTable table = BuildTable();
SqlCommand cmd = GetConnectedCommand();
cmd.CommandText = "usp_MyProc";
cmd.CommandType = CommandType.StoredProcedure;
AddParameter(cmd, "@searchOptionsTable", SqlDbType.Structured, table);
reader = cmd.ExecuteReader();
... snip

Here is the parameter declaration in the stored procedure. I have omitted the declaration of the user-defined data type for the table parameter for brevity. Note that READONLY must be specified on the table parameter.

CREATE PROCEDURE usp_MyProc (@searchOptionsTable uddt_SearchOptionsTable READONLY)

When I tried to run the program, I received a System.NotSupportException exception with the following message: DBNull value for parameter '@searchKeyTypeList' is not supported. Table-valued parameters cannot be DBNull. I stepped through the program with the VS debugger and verified that the table had been created and was being passed properly to the stored procedure.

It turned out that the message was misleading. The table was not null and contained several rows. However, I had not set its TableName property. Once I set that property, everything worked! To fix the problem, add one of the following lines of code to the C# code snippet:

DataTable table = new DataTable("Something");
table.TableName = "Something";

Hopefully, this article will help somebody avoid the frustrating hour or so that I wasted chasing this bug only to find that it was a very simple fix.

Code on!