Retrieving Scalar Data from a Stored ProcedureBy Scott Mitchell
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 own SQL Server database. With relational databases like SQL, commands are issued through the SQL syntax, which includes
DELETEstatements, 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
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
Typically data is returned from a stored procedure using a
SELECTstatement, 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:
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
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 the
function. So, after
INSERTing the new record you can simply return the value like so:
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
store_GetAverageInventoryPrice and grab back the scalar result:
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:
(The above omits a check to see if the result is NULL. If there were no rows in
no rows with a non-NULL
Price, the returned
Object would be equal to
Ideally you would either add such a check to the above code or edit the stored procedure to use
convert any NULL result into a number (i.e.,
SELECT ISNULL(AVG(Price), 0.0) ...).)
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
The following snippet shows a stored procedure that returns the set of inventory items through a
and uses an output parameter to return the average price:
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:
(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
RETURNstatement yourself, however, to return a scalar integer value. For example, let's revisit the
store_AddNewInventoryItem, but modify it to return the ID of the newly inserted row as a return value.
Note that the
SCOPE_IDENTITY() value is being return via a
RETURN statement now, whereas in the
earlier example we used a
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 of
ReturnValue, as the following code snippet
That's all there is to it! As I mentioned earlier, you can only return integer values through the stored procedure's return type.
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
SELECTstatement 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
Directionproperty value. Then, after you call the stored procedure, you can access the retrieved value through the parameter's