Retrieving Scalar Data from a Stored Procedure
By Scott Mitchell
Introduction
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 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 the SCOPE_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
the store_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 to DBNull.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
the store_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 of ReturnValue, 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 SELECT statement, 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.
