Stored Procedure Output Parameters | Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)

Chapter 8 - Using Stored Procedures

byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

So far, we've seen examples of using stored procedures without parameters, and stored procedures with input parameters. Logically, then, the next step in dealing with stored procedures is to develop one that uses output parameters. If you like, you can think of an output parameter as being like using a ByRef parameter in a VB.NET function: it's an input parameter whose value can be changed from within the procedure.

Output parameters can come in handy when we're looking for a single value, rather than a full result set. If, for example, we want to discover the name of a product whose ID we already know, we could use SELECT ProductName FROM Products WHERE ProductID = @ProductID, but that would force us to process a result set containing a single column and a single row. While this is valid, it is also slow. Using output parameters, we can return the ProductName by itself, without any result set being returned to the client.

The technique for using output parameters in ASP.NET code is almost identical to the technique for using input parameters, with a few minor differences in the way things are declared. Before we look at that code, though, we need to examine the stored procedure we're going to create. (Unfortunately, Northwind doesn't contain a suitable sample procedure on this occasion.) We'll write a stored procedure that returns the name of a company in an output parameter, based on the value of the CustomerID that we send as an input parameter.

      CREATE PROCEDURE sp_GetCompanyName @CustomerID nchar(5),                                         @CompanyName nvarchar(40) OUTPUT AS      SELECT @CompanyName = CompanyName FROM Customers WHERE CustomerID = @CustomerID

As you can see, for the most part this procedure is little different from some of the others we've been looking at. The differences come with the OUTPUT keyword, which identifies @CompanyName as an output parameter, and the form of the SELECT statement, in which the first term involves assigning a value to the output parameter. These features are common to the definitions of all stored procedures that involve output parameters, and while you may encounter more complex examples, the syntax will always be the same.

Having created the stored procedure in the database by using the same basic code template that we've used to create our other procedures (see the CreateOutput project in the downloadable code), we can proceed to testing it. At this point, however, things start to become a little trickier. When you right-click on its name in the Server Explorer and run it, you'll be presented with more awkward dialog box:

click to expand

Remember: this procedure doesn't return a result set, so even if we provide a value for the input parameter to this dialog, we won't see any results. Instead, we'll just have to go ahead and create an ASP.NET page that does the job properly. What we want is to use the list box from the last example (but this time filling it with customer IDs, rather than their full names), and to have that execute the stored procedure. The value of the output parameter will then be displayed in a label control. This is OutputParam.aspx:

     <%@ Import Namespace="System.Data" %>     <%@ Import Namespace="System.Data.SqlClient" %>     <html>       <head><title>Using Stored Procedures With Output Parameters</title></head>       <body>         <form runat="server" method="post">           <asp:ListBox  runat="server" Size="1" />           <asp:Button  runat="server"                       Text="Submit" OnClick="Submit" />           <br/><br/>           <asp:Label  runat="server" />         </form>       </body>     </html>     <script language="VB" runat="server">     Sub Page_Load(Source As Object, E As EventArgs)       If Not IsPostBack Then         Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")         Dim objConnection As New SqlConnection(strConnection)         Dim objCommand As New SqlCommand("SELECT CustomerID " & _                                          "FROM Customers", objConnection)         objConnection.Open()         lbCustomers.DataSource = objCommand.ExecuteReader()         lbCustomers.DataTextField = "CustomerID"         lbCustomers.DataValueField = "CustomerID"         lbCustomers.DataBind()         objConnection.Close()       End If     End Sub     Sub Submit(Source As Object, E As EventArgs)       Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")       Dim objConnection As New SqlConnection(strConnection)       Dim objCommand As New SqlCommand("sp_GetCompanyName", objConnection)       objCommand.CommandType = CommandType.StoredProcedure       Dim objParameter As New SqlParameter("@customerid", SqlDbType.NChar, 5)       objCommand.Parameters.Add(objParameter)       objParameter.Direction = ParameterDirection.Input       objParameter.Value = lbCustomers.Selecteditem.Value

So far, we've seen precious little change from the previous example. Now, however, we need to create the output parameter that will hold the output value from the stored procedure. It's a simple matter of setting the Direction property accordingly:

      Dim objOutputParameter as new SqlParameter("@CompanyName", _                                                 SqlDbType.NVarChar, 40)      objCommand.Parameters.Add(objOutputParameter)      objOutputParameter.Direction = ParameterDirection.Output

We then open our connection and execute our stored procedure, but this time we don't need to return a reader object - there is no result set. Be aware, though, that it's quite acceptable for a stored procedure to return a result set and have an output parameter; we just haven't chosen to do that here.

        objConnection.Open()        objCommand.ExecuteReader()

At this stage, we can set the text of the label either by using the named object - in this case objOutputParameter.Value - or by accessing the Parameters collection through the use of an expression like objCommand.Parameters("@CompanyName").Value. Both of these approaches achieve the same results, so it comes down to personal preference.

     lblOutput.Text = objOutputParameter.Value     objConnection.Close()   End Sub   </script>

When you run this example, you'll have a label that changes depending on the ID that's picked from the list box.

click to expand

At this point, you should be able to use both input and output parameters with confidence, but let's try one last example just to make sure that we're all comfortable.

Try It Out - a Stored Procedure with Output Parameters

In this final example, we'll create a stored procedure that's very similar to the one we used earlier to return customer names from selected states. However, we will adapt it so that it also returns the number of rows that were displayed for a particular state.

We're going to use a built-in global variable called @@rowcount to determine the number of rows returned by our procedure. In programming terms, this means adding an additional parameter, and setting that parameter to @@rowcount. Our stored procedure will therefore look like this:
```
 CREATE PROCEDURE sp_CustomersByStateWithCount @region nvarchar(15),                                               @matches int OUTPUT AS SELECT CustomerID, CompanyName FROM Customers WHERE region = @region ORDER BY CompanyName SET @matches = @@rowcount 
```
Once again, we can build a quick VB.NET application to create this stored procedure in exactly the same way as we've done for all the other examples.

We can now look at the ASPX page we will create to execute and display the results of this procedure. Again, this page is very similar to the page we created earlier, but there are a few important additions:

We need to display the number of matches returned from the stored procedure.
We need to add a label control to handle the output, and we also need to change the Submit () procedure to accept the output parameter.
We also need to change the name of the stored procedure that we want to execute, and create the corresponding output parameter.

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html>   <head><title>Using Stored Procedures With Output Parameters</title></head>   <body>     <form runat="server" method="post">       Enter a State Code:       <asp:Textbox  runat="server" />       <asp:Button  runat="server"                   Text="Search" OnClick="Submit" />       <br/><br/>       <asp:label  runat="server" />       <br/><br/>       <asp:DataGrid  runat="server" />      </form>   </body> </html> <script language="VB" runat="server"> Sub Submit(Source As Object, E As EventArgs)   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim objConnection As New SqlConnection(strConnection)   Dim objCommand As New SqlCommand("sp_CustomersByStateWithCount",objConnection)   objCommand.CommandType = CommandType.StoredProcedure   Dim objParameter As New SqlParameter("@region", SqlDbType.NVarChar, 15)   objCommand.Parameters.Add(objParameter)   objParameter.Direction = ParameterDirection.Input   objParameter.Value = txtRegion.text   Dim objOutputParameter As New SqlParameter("@matches", SqlDbType.Int)   objCommand.Parameters.Add(objOutputParameter)   objOutputParameter.Direction = ParameterDirection.Output   objConnection.Open()   Dim objDataReader As SqlDataReader   objDataReader = objCommand.ExecuteReader()   dgOutput.DataSource = objDataReader   dgOutput.DataBind()   objCommand.Connection.Close()   objCommand.Connection.Open()   objCommand.ExecuteNonQuery()   lblRecords.Text = "Matches: " & CInt(objCommand.Parameters(1).Value)   objConnection.close() End Sub </script>

Save or download the above listing as OutputStates. aspx, and save it in your ch08 folder.

When you run this code and enter the abbreviation for a US state in the text box, you'll see something like the following:

How It Works

As always, the keys to dealing with output parameters are to remember to set the parameter to an output direction, to set the correct data type, and to add them to the command object's Parameters collection. With that done, we can continue processing the command object, and return the result set and the output parameter. The final step is to output the @matches variable into the label control for display.

       lblRecords.Text = "Matches: " & CInt(objCommand.Parameters(1).Value)

Essentially, this example works just like the previous one, with the addition of the output parameter. When the stored procedure is called, the output parameter is populated and returned. From that point, it's just a matter of getting access to the parameter through the command object's Parameters collection, as above.