It is easy to extol the virtues of stored procedures (a.k.a. sprocs) in any modern database. Query optimizers perform a number of complicated tasks in an attempt to make your queries run in the most efficient manner. Sprocs store this optimized query plan so that it doesn’t have to be refigured every time the query is executed. Storing the plan gives you a significant performance gain that alone makes sprocs an attractive choice for your application design. But sprocs also allow you to control how data is retrieved or updated in your database.

Many developers shy away from working with sprocs because their use requires writing more code than just issuing inline SQL statements. However, the performance gains make working with sprocs worthwhile. Let’s take a look at using four different sprocs: a simple sproc that returns multiple records in a single result set, a sproc that returns multiple result sets, a sproc that accepts an input parameter, and a sproc that returns data via output parameters.

Working with a simple sproc
ADO.NET includes a SqlCommand object (as well as objects specifically for OleDB, Oracle, and ODBC) that is the key way to work with sprocs in SQL Server. Let’s begin by using SqlCommand to execute a sproc that simply returns one or more records using a SELECT statement. Using SQL Server’s SQL Query Analyzer, switch to the sample Northwind database and enter the following code to create a sproc called AllProducts:
Select * from Products

With ADO.NET, you could issue an inline SELECT statement in your code, but with a sproc, your ADO.NET code will look slightly different. All the code for this article is included in a downloadable Windows application project. You can find the full code for this scenario tied to the Simple Sproc button on the form, but I’ve placed the most interesting code in Listing A.

Notice that I am creating a Connection and Command object in the same way I would normally. However, instead of setting the CommandText property to the SQL statement, I set it to the name of the sproc, AllProducts, and then inform ADO.NET that the CommandType is a stored procedure.

At this point, I can simply use the ExecuteReader method of the Command object to call the sproc. Because the AllProducts sproc returns a standard result set from SQL Server, I can store this result in a DataReader object. I can then use the DataReader’s Read to access the data, just as I would with inline SQL. Pretty minor changes so far, but lets look at a more complex example.

Returning multiple result sets
You can have multiple queries inside a single sproc, as shown in the following sproc code:
Select * from Products;
Select * from Customers;

Note that each query is terminated by a semicolon (;). You could call this sproc in the same way we called the sproc in the previous example, but the DataReader will at first be accessing only the first result set. In other words, you’ll be looking at the records from the first query, the Products table. To access the records from the second Customers table query, I’ll need to call the DataReader.NextResult method. The entire source for this example is tied to the Multiple Tables button in the downloadable sample project, but the interesting code is featured in Listing B.

In this example, I call the ProdsAndCusts sproc, and after reading all the records returned from the Products table, I call the NextResult method and begin reading the records from the Customers table. The full code places all these records in a list box with a header before each table, as shown in Figure A.

Figure A
Multiple tables example

Working with input parameters
Hard-coding a sproc in such a way that it always returns a fixed result set is pretty limiting. Sometimes, you’ll want to pass in a value, such as a Product ID, to receive a specific record or records from the sproc. You’ll first need to create a parameter in the sproc itself and pass in a value when you call the procedure. SQL Server requires that parameters be defined with the at symbol (@) and given a data type, as in the following sproc code:
@ProductID int
SELECT p.ProductName,
FROM Products p INNER JOIN Suppliers s ON
        p.SupplierID = s.SupplierID
     INNER JOIN Categories c ON
        p.CategoryID = c.CategoryID
WHERE p.ProductID=@ProductID

Now I have a query that returns data from three different tables and limits the data to only a single product. Of course, I have to do some additional work in ADO.NET; namely create a new Parameter object and add it to the Command object’s Parameters collection. The full code is tied to the Input Param button in the downloadable source, but the interesting code can be found in Listing C.

Notice that when I add the parameter to the Parameters collection, I used the same name and value type as the parameter defined in the sproc. Then I set a value for the parameter that will be sent to the sproc and used in the queries it contains.

Output parameters
Not only can you use parameters to send in a value and control the returned data, you can also use a parameter to return a single value. Output parameters are useful when you aren’t interested in an entire record but only in a few fields found in that record. Note that this approach will work only if you want to return a single record.

To use output parameters, I’ll define some more parameters in my sproc and give them a direction of OUTPUT. Here’s the code for my new sproc:
@ProductID int,
@ProductName nvarchar(40) OUTPUT,
@CompanyName nvarchar(40) OUTPUT,
@CategoryName nvarchar(15) OUTPUT,
@UnitPrice money OUTPUT
SELECT @ProductName=p.ProductName,
FROM Products p INNER JOIN Suppliers s ON
        p.SupplierID = s.SupplierID
     INNER JOIN Categories c ON
        p.CategoryID = c.CategoryID
WHERE p.ProductID=@ProductID

Notice that now I have five parameters, one input and four output. The output parameters match the fields I am interested in returning from the query. To work with this in ADO.NET, I’ll also need to create five Parameter objects, one for the input parameter and one for each of the output parameters. The full code for this example is attached to the Output Params button in the downloadable sample, but Listing D has the important pieces you should examine.

Notice that when I add the output parameters, I also specify the data type (and the size if the parameter is a character type). Then, I specify the direction for each output parameter using the Direction property. I don’t need to specify a direction for the input parameter, since ParameterDirection.Input is the default.

The other key point you should notice is that I am not calling the ExecuteReader method to execute the sproc here. Instead, I call ExecuteNonQuery to avoid the overhead involved in creating a DataReader object to handle the returned data, making my data access code very fast. The values for the output parameters are returned via parameter objects I created instead. You can see the results of running this code in the sample application in Figure B.

Figure B
Data returned by output parameters

Using sprocs requires some additional coding in ADO.NET, especially if the stored procedure uses parameters. However, the performance benefits and additional control over accessing and updating data typically makes the trade-off worthwhile. In those cases where you need to return only one record, don’t forget about the huge performance benefit of using output parameters over creating a DataReader to handle a single record.