Writing a Stored Procedure

Virtually all ASP.NET applications of interest work with database data at some level, and one of the most common databases used in ASP.NET applications is Microsoft's ownSQL Server database. With relational databases like SQL, commands are issued through the SQL syntax, which includes SELECT, INSERT, UPDATE, and DELETE statements, among others. One way to issue a command to a database from an ASP.NET application is to craft the SQL query in the application itself. Such queries are often called ad-hoc queries. The primary downside of ad-hoc queries is that they are hard to maintain - if you need to change your query you need to edit the string in your application, recompile, and redeploy.
A better approach, in my opinion, is to use stored procedures. Stored procedures are pre-compiled functions that reside on the database server that can be invoked by name. This is similar to compartmentalizing programmatic functionality into methods. Stored procedures are not only more updateable than their ad-hoc counterpart, but also can be utilized by other applications. For example, you might have both an ASP.NET application and a Web services application that is driven on data from the same database. If you hard code your SQL queries in your source code, any changes will now require modifications in two places (as well as two places that now require recompilation and redeployment). However, by using stored procedures there's a single point that needs modification. (The debate between stored procedures and ad-hoc queries has been done in much greater detail in other venues; see Rob Howard's blog entry Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome) for a pro-stored procedures slant, and Frans Bouma's entry Stored Procedures are Bad, M'Kay? for a look at why stored procedures aren't the end-all answer.)
Stored procedures typically return resultsets, such as the results of a SELECT query. However, there are times when you may be getting back just scalar data from a stored procedure. For example, you might have a stored procedure that returns just the account balance for a particular customer, or one that returns the average age of all users in your database. When calling a stored procedure that INSERTs a new record into a table with an IDENTITY field, you may want to get back the ID for the newly inserted row.
There are a couple of ways to get back scalar data from a SQL Server stored procedure. In this article we'll look at these various techniques along with how to fetch back the returned data in your ASP.NET code. Read on to learn more!

Returning Data with a SELECT Statement

Typically data is returned from a stored procedure using a SELECT statement, and typically the data returned is a resultset, consisting of multiple fields and records. For example, a stored procedure might be created to get all products in inventory, which might be accessed through the SQL query:

CREATE PROCEDURE store_GetInventory AS SELECT InventoryID, ProductName, Price, UnitsOnStock FROM store_Inventory

However, there's no reason why you can't return a simple scalar value. For example, if you were interested in the average price of all items in inventory - i.e., just a simple number, like $11.92 - you could return this scalar data using a SELECT statement:

CREATE PROCEDURE store_GetAverageInventoryPrice AS SELECT AVG(Price) AS AveragePrice FROM store_Inventory

Similarly, in stored procedures that insert a new record into a table that has an IDENTITY field, you can get the ID value of the newly inserted record through theSCOPE_IDENTITY() function. So, after INSERTing the new record you can simply return the value like so:

CREATE PROCEDURE store_AddNewInventoryItem ( @ProductName nvarchar(50), @Price money ) AS -- INSERT the new record INSERT INTO store_Inventory(ProductName, Price) VALUES(@ProductName, @Price) -- Now return the InventoryID of the newly inserted record SELECT SCOPE_IDENTITY()

When returning scalar through a SELECT statement you can retrieve the data using the exact same technique used to retrieve a resultset. That is, you can, if you want, use a DataReader, DataTable, or DataSet. The only thing to keep in mind is that you're results will contain only one row with only one field. The following code would call thestore_GetAverageInventoryPrice and grab back the scalar result:

Dim myConnection as New SqlConnection(connection string) myConnection.Open() Dim myCommand as New SqlCommand("store_GetAverageInventoryPrice", myConnection) myCommand.CommandType = CommandType.StoredProcedure Dim reader as SqlDataReader = myCommand.ExecuteReader() 'Read in the first record and grab the first column Dim avgPrice as Decimal If reader.Read() Then avgPrice = Convert.ToDouble(reader("AveragePrice")) End If

This is a bit of overkill, though, thanks to the DataCommand's ExecuteScalar() method. The ExecuteScalar() method can be used in place of the ExecuteReader(), the difference being ExecuteScalar() returns a single Object instance as opposed to a DataReader. Using ExecuteScalar() the code would be simplified to:

Dim myConnection as New SqlConnection(connection string) myConnection.Open() Dim myCommand as New SqlCommand("store_GetAverageInventoryPrice", myConnection) myCommand.CommandType = CommandType.StoredProcedure Dim avgPriceObject as Decimal = Convert.ToDecimal(myCommand.ExecuteScalar())

(The above omits a check to see if the result is NULL. If there were no rows in store_Inventory or no rows with a non-NULL Price, the returned Object would be equal toDBNull.Value. Ideally you would either add such a check to the above code or edit the stored procedure to use ISNULL to convert any NULL result into a number (i.e., SELECT ISNULL(AVG(Price), 0.0) ...).)
While the SELECT method just discussed provides an easy way to return a scalar value from a stored procedure it only works if the scalar value is the sole piece of data you want to return from the stored procedure. There are times, however, where you want to return a full resultset from the stored procedure along with some scalar value. The remaining two approaches we'll be looking at in this article address how to accomplish this feat.
Using Output Parameters One way to retrieve scalar data in addition to a standard resultset from a stored procedure is to use one or more output parameters. An output parameter is a parameter that is passed into the SQL stored procedure, but whose value can be set in the stored procedure. This assigned parameter, then, is readable back from the application that called the stored procedure.
To use an output parameter you need to indicate that the parameter is intended for output via the OUTPUT keyword. The following snippet shows a stored procedure that returns the set of inventory items through a SELECT statement and uses an output parameter to return the average price:

CREATE PROCEDURE store_GetInventoryWithAveragePrice ( @AveragePrice money OUTPUT ) AS SET @AveragePrice = (SELECT AVG(Price) FROM store_Inventory) SELECT InventoryID, ProductName, Price, UnitsOnStock FROM store_Inventory

To access the value of an output parameter from your ASP.NET application you need to create a parameter object whose Direction property is set to Output. After you call the stored procedure the output parameter's value is accessible through the Value property, as the following code illustrates:

Dim myConnection as New SqlConnection(connection string) myConnection.Open() Dim myCommand as New SqlCommand("store_GetInventoryWithAveragePrice", myConnection) myCommand.CommandType = CommandType.StoredProcedure 'Create a SqlParameter object to hold the output parameter value Dim avgPriceParam as New SqlParameter("@AveragePrice", SqlDbType.Money) 'IMPORTANT - must set Direction as Output avgPriceParam.Direction = ParameterDirection.Output 'Finally, add the parameter to the Command's Parameters collection myCommand.Parameters.Add(avgPriceParam) 'Call the sproc... Dim reader as SqlDataReader = myCommand.ExecuteReader() 'Now you can grab the output parameter's value... Dim avgPrice as Decimal = Convert.ToDecimal(avgPriceParam.Value)

(The same issue regarding NULLs applies here as in the previous example...)
You are not limited to a single output parameter; additionally, you can have stored procedures with both input and output parameters.

Using a Return Value

The final technique I want to talk about for returning scalar values from a stored procedure is using return values. Whenever a stored procedure finishes executing, it always returns a return value. This return value is, by default, 0. You can use the RETURN statement yourself, however, to return a scalar integer value. For example, let's revisit thestore_AddNewInventoryItem, but modify it to return the ID of the newly inserted row as a return value.

CREATE PROCEDURE store_AddNewInventoryItem ( @ProductName nvarchar(50), @Price money ) AS -- INSERT the new record INSERT INTO store_Inventory(ProductName, Price) VALUES(@ProductName, @Price) -- Now return the InventoryID of the newly inserted record RETURN SCOPE_IDENTITY()

Note that the SCOPE_IDENTITY() value is being return via a RETURN statement now, whereas in the earlier example we used a SELECT.
To retrieve the return value from a stored procedure use the same technique as with output parameters, the only difference being that you should use a Direction value ofReturnValue, as the following code snippet illustrates:

Dim myConnection as New SqlConnection(connection string) myConnection.Open() Dim myCommand as New SqlCommand("store_GetInventoryWithAveragePrice", myConnection) myCommand.CommandType = CommandType.StoredProcedure 'Create a SqlParameter object to hold the output parameter value Dim retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int) 'IMPORTANT - must set Direction as ReturnValue retValParam.Direction = ParameterDirection.ReturnValue 'Finally, add the parameter to the Command's Parameters collection myCommand.Parameters.Add(retValParam) 'Call the sproc... Dim reader as SqlDataReader = myCommand.ExecuteReader() 'Now you can grab the output parameter's value... Dim retValParam as Integer = Convert.ToInt32(retValParam.Value)

That's all there is to it! As I mentioned earlier, you can only return integer values through the stored procedure's return type.

Conclusion

In this article we examined three ways to pass back scalar data from a stored procedure, along with the necessary code to process the returned value. You can use a SELECTstatement, output parameter, or return value (assuming you want to pass back an integer value). When returning a scalar value via a SELECT statement you can read the resulting value using the ExecuteScalar() method. For output parameters and return values you need to create a parameter object with the proper Direction property value. Then, after you call the stored procedure, you can access the retrieved value through the parameter's Value property.