Passing Parameters to a Stored Procedure | Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)

Chapter 8 - Using Stored Procedures

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

In this section, we'll continue to develop the sp_WaCustomers stored procedure. It might be nice to know about the customers in Washington customers, but what about the ones in Oregon? Do we have to create a procedure called sp_OrCustomers as well? Actually, we don't. By rewriting the stored procedure to accept the state code as a parameter, we can create a much more manageable and flexible solution.

The first order of business is to create the new stored procedure, and as a starting point we can use something even simpler than sp_WaCustomers. The following SQL statement creates a stored procedure called sp_CustomersByState that returns all of the customers in the database, in alphabetical order of their company names.

    CREATE PROCEDURE sp_CustomersByState AS    SELECT CustomerID, CompanyName FROM Customers ORDER BY CompanyName

We seem to have moved further away from our goal, but don't worry. In order to get the list of customers filtered correctly, we're going to change this procedure to use an SQL variable. As you're about to see, variables in SQL work in much the same way as they do in other programming languages.

SQL Variables

SQL variables are declared using the DECLARE statement. For SQL Server (and MSDE) databases, the full syntax is:

    DECLARE @var_name As data_type

All variable names must be preceded with an @ symbol in order to be valid. Global variables are denoted with two such symbols (@@). The data type must be a valid SQL Server data type, such as int, datetime, char, varchar, or money.

Note

Every DBMS has its own rules for variables. Both DB2 and Oracle use a different syntax from SQL Server.

The purpose of the variable we're creating is to represent the code of the region we're interested in, so we might declare it with code like this:

    DECLARE @region as nvarchar(15)

What's an nvarchar? To find the answer to that, we need to take a couple of steps backwards. An SQL char deals with character data - the number in parentheses specifies how many characters the variable can contain. If we were to declare a char(10), enough space to hold ten characters would always be allocated for the data, even we chose only to store one.

Moving on, an SQL varchar also stores character data, but only uses as much space as it needs. If we declared a variable as a varchar(10), but assigned a single character to it, only the space required to hold that character would be allocated.

These two data types work just fine, as long as we never have to deal with non-roman character sets. If we need to use a character set such as Kanji, on the other hand, we need to use a Unicode character set, in which every character requires twice as much space to store it compared with the schemes outlined above. Since Northwind Traders is a global company, they need to use international character sets, and therefore they need to store characters in Unicode format. We declare our @region variable as an nvarchar(15) because the Customers table defines the region as being that long.

Now that we have the @region variable, we can use it in the SQL statement. For ease of testing, let's set the value of @region to 'WA' by using the SET statement to define the value of our variable:

 CREATE PROCEDURE sp_CustomersByState AS DECLARE @region nvarchar(15) SET @region = 'WA' SELECT CustomerID, CompanyName FROM Customers WHERE region = @region ORDER BY CompanyName

With these changes in place, we can get hold of our Washington customers again, but we're also much closer to a final solution. We need to make a final change so that we can pass a value for the @region variable into the procedure, and we do this by turning the DECLARE statement into a parameter definition, like so:

    CREATE PROCEDURE sp_CustomersByState @region nvarchar(15) AS    SELECT CustomerID, CompanyName FROM Customers    WHERE region = @region ORDER BY CompanyName

We can test this procedure by including it in a new VB.NET application called CreateByState, which follows the same pattern as the stored procedure-creating application we used earlier. The only change you need to make is to put the new stored procedure in the sql string:

         cn = New SqlConnection("Data Source=(local)\NetSDK;" & _                                "Initial Catalog=Northwind;Integrated Security=SSPI")         cn.Open()         sql = "CREATE PROCEDURE sp_CustomersByState @region nvarchar(15) AS " & _               "SELECT CustomerID, CompanyName FROM Customers " & _               "WHERE region = @region ORDER BY CompanyName"         cmd = New SqlCommand(sql, cn)         cmd.ExecuteNonQuery()         Console.WriteLine("Procedure created!")

In order to test that this works, compile and execute the code, and then right-click on the new stored procedure in the Server Explorer and run it. When you do this, you'll see something new: Visual Studio will ask you for the value of the parameter, via the following message box:

click to expand

If we pass WA as a parameter, this gets us the Washington customers:

 CustomerID CompanyName ---------- --------------------------------------- LAZYK      Lazy K Kountry Store TRAIH      Trail's Head Gourmet Provisioners WHITC      White Clover Markets

But we can also find the Oregon customers by using the same procedure but entering OR into the dialog box:

 CustomerID CompanyName ---------- --------------------------------------- GREAL      Great Lakes Food Market HUNGC      Hungry Coyote Import Store LONEP      Lonesome Pine Restaurant THEBI      The Big Cheese

As you can begin to see, we now have a reusable stored procedure that we can use to find Northwind's customers in any given state.

Passing Parameters from Web Pages

The next step is to create a web page that allows the user to enter a state code, and then performs the lookup for this information. We will need a text box and a button, in addition to our usual data grid. Assuming that you've set up the sp_CustomersByState stored procedure as detailed above, you should have no trouble assembling the following example.

Try It Out - Passing a Parameter to a Stored Procedure

What we want to be able to do is to key in a state and then press the button to re-filter the data grid. With that in mind, the text box will hold our parameter information, and we'll be sending that back first to the web server, and then to MSDE.

Here's the HTML section of the ASPX page we're working on. Enter it into a new text file, and save it in your ch08 folder, with the name StateFilter.aspx:

 <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>   <head><title>Using Stored Procedures With 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:DataGrid  runat="server" />     </form>   </body> </html>

In our VB.NET code, we'll need to use a new class called System.Data.SqlClient.SqlParameter. Objects of this class are designed to represent a parameter in a stored procedure, and the constructor therefore needs to be told about the name, the data type, and the size of the parameter in question.

We know all of this information already. The name is @region (don't forget the @ symbol), the type is nvarchar, and the size is 15 characters. Before we can execute our stored procedure, however, we need to take an extra step to add the parameter to the collection that's maintained by the command object. Let's look at the VB.NET code, which should be placed after the HTML in the StateFilter.aspx file, and step through this process.
```
 <script language="VB" runat="server"> Sub Submit(ByVal Source As Object, ByVal E As EventArgs)   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim objConnection As New SqlConnection(strConnection)   Dim objCommand As New SqlCommand("sp_CustomersByState", objConnection)   objCommand.CommandType = CommandType.StoredProcedure 
```

So far, that should all be looking pretty familiar. Next, though, we need to create a parameter object, and add it to the command object's Parameters collection.

   Dim objParameter As New SqlParameter("@region", SqlDbType.NVarChar, 15)   objCommand.Parameters.Add(objParameter)   objParameter.Direction = ParameterDirection.Input   objParameter.Value = txtRegion.Text

At this point, we can open the database connection, execute the reader, and then populate the data grid. The code here is exactly the same as the code we've been using elsewhere for this purpose.
```
   objConnection.Open()   dgOutput.DataSource = objCommand.ExecuteReader()   dgOutput.DataBind()   objConnection.Close() End Sub </script> 
```
If we run this page and search for all of Northwind's Oregon-based customers, we can see that it works as we intended:

How It Works

The lines of code that set this example apart from the others we've looked at are those that deal with passing the parameter:

       Dim objParameter As New SqlParameter("@region", SqlDbType.NVarChar, 15)       objCommand.Parameters.Add(objParameter)       objParameter.Direction = ParameterDirection.Input       objParameter.Value = txtRegion.Text

The first line here creates a new parameter called @region declared as an nvarchar(15), matching our declaration in the stored procedure. The second argument to this version of the constructor is always a member of the System.Data.SqlDbType enumeration, which has 24 members for representing all the data types you could ever need.

The second line then adds the parameter to the Parameters collection of the command object - forgetting to do this is an easy mistake to make! On the third line, we set the parameter object's Direction property, which determines whether it will be used to pass information to the stored procedure, or to receive information from it. ParameterDirection.Input is actually the default for this property, but from a maintenance and readability standpoint, it's helpful to put it in the code. Finally, we set the Value property of the parameter to the text property of our txtRegion text box.

When you enter a code into the text box and press the Search button, the form is submitted back to the ASPX page. The value in the text box is then passed as a parameter to the stored procedure; we execute the code, rebind to the data grid, and our filtered results are displayed. If the stored procedure in question were to support additional parameters, you'd just need to add further SqlParameter objects to your VB.NET code.

Try It Out - Passing a Parameter to a Built-in Procedure

To make sure that this technique is clear in your mind, we'll create an ASPX page that executes one of the Northwind database's built-in stored procedures, with a bit of a twist. We're going to create a list box that shows the names of all the customers in the system, and make arrangements so that when we select one, it will be passed as a parameter to the CustOrdersOrders stored procedure. This returns a list of all the orders for the chosen customer, which we'll present to the user.

First, create a new ASPX file called StoredprocParam. aspx, and save it in the ch08 folder.

After importing the appropriate namespaces, we need to create the input page. A simple list box, a button, and a data grid will do the trick:

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html>   <head><title>Using a Built-in Stored Procedure With 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:DataGrid  runat="server" />     </form>   </body> </html>

Next, the VB.NET code in our Page_Load () handler needs to populate the list box (lbCustomers) with customer names:

 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, CompanyName " &                                      "FROM Customers", objConnection)     objConnection.Open()     lbCustomers.DataSource = objCommand.ExecuteReader()     lbCustomers.DataTextField = "CompanyName"     lbCustomers.DataValueField = "CustomerID"     lbCustomers.DataBind()     objConnection.Close()   End If End Sub

In response to the button being clicked, we need to submit the form and execute a stored procedure that takes the value of the list box as its input parameter. This Submit () procedure differs from the one in the previous example only by the name of the procedure being called, and the setup of the Parameters collection:

 Dim objConnection As New SqlConnection(strConnection) Dim objCommand As New SqlCommand("CustOrdersOrders", 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 objConnection.Open()

When you run this page for the first time, you'll see a list box and a button:
After selecting the first entry and pressing the Submit button, the data grid will be populated with these results:

How It Works

As you probably noticed while we were going through the code above, there's very little in this example that you haven't seen before in this or earlier chapters. Apart from the difference in the way data gets sent to the stored procedure (a list box, rather than a text box), the new feature is the change to the name and type of the parameter when we set up the SqlParameter object:

    Dim objParameter as new SqlParameter("@customerID", SqlDbType.NChar, 5)    objCommand.Parameters.Add(objParameter)    objParameter.Direction = ParameterDirection.Input    objParameter.Value = lbCustomers.SelectedItem.Value

This time, the parameter is called @customerID, and its type is nchar(5), which corresponds to the SqlDbType.NChar argument passed to the constructor in the code above.