The problem of Microsoft C# Datasets and SQL Server stored procedures which use temporary tables

In certain cases when coding up stored procedures I sometimes needed to create a temporary table "on the fly" to hold specialized output (generally I've done this when I needed to scan several tables looking for "most recent" records from each or needed to compose a table of summary information gathered from various tables in the database). To date I've been used to using the T-SQL "create #temptable" syntax.

Traditionally the coding of these small sprocs consisted of 1) creating this temp table, 2) adding rows to it, and 3) ending the sproc with a "select * #temptable" statement to output the results.

This has worked fine for me for years for handling custom summary queries but I discovered that if I tried adding these types of sprocs to a C# dataset I couldn't get them to work. Specifically, when I added the sproc to a project's dataset in Visual Studio 2008 the "wizard" would dutifully add the procedure itself to a "QueriesTableAdapter" collection but would not create an associated table.

This made sense, I guess, since ADO really has no idea what the schema of "#temptable" is in the sproc and could not create a strongly-typed table object from it.

The solutions found in Google searches were not satisfactory until I came across the new (for me at least -- it's actually been around since SQL Server 2005) SQL Server "table variable." It's a significantly more elegant solution than "temptables."

Start the coding of the sproc with the definition of a "table variable" to hold the summary information, e.g.:

DECLARE @AGENCYSUMMARY TABLE
(
agencycode varchar(50)
, entitycode varchar(50) PRIMARY KEY
, scanjobid int
, rows int
, timein datetime
, timeout datetime
)

then write the code to add rows to this table and, at the end of the sproc, add a SELECT statement to dump all the information in the table variable to the output stream:

SELECT agencycode
, entitycode
, scanjobid
, rows
, timein
, timeout
from @AGENCYSUMMARY

Now when you edit a dataset using Visual Studio and add this sproc to the design screen the tableadapter AND the table will appear. Evidently the initial definition of the schema of the table variable provides the dataset with what it needs to handle this "temporary" table and it creates a strongly-type table object just as it would for any other table defined in the database.