Stored Procedures

In order for any server component to be useful to multiple applications, it must accept and return parameters (values). Stored procedures are no different, and both accept and return parameters and values. Using parameters allows us to limit the amount of data that the stored procedure must select from tables and work with. Stored procedures can contain input and output parameters and return values.

Input parameters are the default and we do not have to specify anything specific when creating input parameters in our stored procedures. All we need to do is specify the data type that the parameter represents. Let's look at the following code fragment. This specifies a stored procedure name that is to be created and specifies one input parameter. The at sign prefixing the parameter name, @state_name, is required and signifies a local variable. Notice that we have specified the data type for this parameter – this is also required. The parameters are always enclosed in parentheses:

CREATE PROCEDURE up_parmsel_state_abbreviation (@state_name VarChar(15),
   @state_code Char(2) OUTPUT) AS


Output parameters have the same required syntax as input parameters, plus they require the OUTPUT keyword. In the code fragment above, the @state_code parameter is an output parameter, as is evident by the OUTPUT keyword.

Return values indicate a return code from the stored procedure. The return value does not have to be specified as the parameters do. We simply use the RETURN SQL statement to return a value. This value has to be an Integer data type and can return any value you need. For example, this value can be a return code, the number of rows affected by a SQL statement, or the number of rows in a table. Basically any integer data that you want to return can be specified in a return value in your stored procedure.

The following code fragment shows a stored procedure returning a return code that has been set in the local variable @rc:

Return @rc


Let's apply our knowledge gained thus far about stored procedure parameters and return values by trying a hands-on example.

Try It Out – Stored Procedure Parameters and Return Value

We will create a stored procedure named up_parmsel_state_abbreviation that will accept the state name as an input parameter and return the state abbreviation as an output parameter. This stored procedure will return a return code of zero if the stored procedure was successful, and a return code of one if the stored procedure was not successful.

1.    Open the Query Analyzer as described in Chapter 5. Ensure you select the Wrox_Auto_Works database in the database name combo box.

2.    The code for the stored procedure is shown below, and is also available for download as up_parmsel_state_abbreviation. Enter this code into the query pane and run it. This will create this stored procedure:

CREATE PROCEDURE up_parmsel_state_abbreviation (@state_name VarChar(15),
   @state_code Char(2) OUTPUT) AS
-- Declare variables
DECLARE @rc Int
SELECT @state_code = State_Abbreviation_CH
   FROM State_T
   WHERE State_Name_VC = @state_name
IF @@RowCount > 0
   BEGIN
   SELECT @rc = 0
   RETURN @rc
   END
ELSE
   BEGIN
   SELECT @rc = 1
   RETURN @rc
   END


3.    When you create an object in SQL Server, you are the owner of that object. No one else except the database administrator has access to that object. Therefore, you must grant permission for other users to access your object, as was discussed earlier in this chapter and also in Chapter 4.

In Enterprise Manager, expand the Wrox_Auto_Works database and click on Stored Procedures in the tree view:

4.    Find your stored procedure in the list in the details pane and right click on it. Choose All Tasks from the popup menu. On the sub menu, choose Manage Permissions.

5.    Notice that all Roles and all Users are listed. In keeping our administrative tasks simple, we only want to grant execute permissions to our stored procedure to the Developers, Management, and Sales Staff roles, and we do this by checking the checkbox next to each role. Click on the OK button to close the dialog. Now all three roles have permission to execute the stored procedure we have created.

How It Works – Stored Procedure Parameters and Return Value

The first line of code specifies that a stored procedure is to be created, and that it has an input parameter called @state_name, which is a VarChar field that can contain up to 15 characters. The second parameter, @state_code, is an output parameter, as specified by the OUTPUT keyword, and is a Char field that contains two characters:

CREATE PROCEDURE up_parmsel_state_abbreviation (@state_name VarChar(15),
   @state_code Char(2) OUTPUT) AS


Unlike VB, we must declare all variables that we are going to use, and this next line of code declares a variable for our return code. Notice that we must also specify the data type, which in this case is an integer as all return values must be Integer data types:

-- Declare variables
DECLARE @rc Int


Next we are selecting the State_Abbreviation_CH column into our output parameter, @state_code, from the State_T table, but only if the State_Name_VC column equals the state name passed in the input parameter @state_name:

SELECT @state_code = State_Abbreviation_CH
   FROM State_T
   WHERE State_Name_VC = @state_name


We use the built-in function @@RowCount to determine the number of rows affected by the last SQL statement. If a row was found then @@RowCount will return 1 indicating that one row was found, otherwise it will return 0, which indicates that no rows were found. We are using an IF ELSE condition to set our return code and end our stored procedure.

In SQL Server, an IF ELSE condition is not terminated with an END IF like in VB. Instead it ends with the last statement in the ELSE condition. If you want to process more than one SQL statement in an IF ELSE condition, you must specify a BEGIN and END block. This ensures that all SQL statements within the BEGIN END block are executed. We'll be using BEGIN END blocks more later in the book.

If a match was found from the SELECT statement, we are going to set our return value, @rc, to zero and return to the caller. The RETURN SQL statement is used to return the return code to the caller. This SQL statement halts execution immediately and no statements following RETURN get executed. Thus you can use RETURN to exit your stored procedure immediately upon determining an error.

Reminder: Return values must be an Integer data type.

IF @@RowCount > 0
   BEGIN
   SELECT @rc = 0
   RETURN @rc
   END


If we did not find a match, we are going to return with a return code of 1:

ELSE
   BEGIN
   SELECT @rc = 1
   RETURN @rc
   END


This stored procedure does not have to use a variable to return the return code; we could simply specify RETURN 0 or RETURN 1. We used the @rc variable in this exercise to introduce you to local variables. We will be working more with local variables as we move on.

Try It Out – Creating a VB Form to Call the Stored Procedure

Now that we have our stored procedure coded and permissions set so other users can access it, it's time to code the VB front-end program to execute our stored procedure.

1.    Create a Standard EXE VB program to call the stored procedure just created. On the form, place two labels and two textboxes with the following properties:

Control	Name	Caption or Text
Label	Label1	State Abbreviation
Label	Label2	Return Code
TextBox	txtAbbreviation
TextBox	txtReturnCode

2.    Change the caption of the form if you wish. Your form should now look similar to this, but without the data values in the textboxes:

3.    Set a reference to Microsoft ActiveX Data Objects Library version 2.5, or the latest version you have. We saw how to do this in Chapter 6.

4.    The code which you need to enter into your VB form is presented step by step in the How It Works section below, and is available as frmTryItOut1.frm.

How It Works – Creating a VB Form to Call the Stored Procedure

This section presents and explains the code that makes up the VB form. As with the previous examples we have coded, we are using the Form_Load event to keep things simple. We start by setting the form icon to nothing and declaring our database objects. Notice that this time, instead of the Recordset object, we are using the Command object:

Private Sub Form_Load()
   'Use the standard Windows icon
   Me.Icon = Nothing
   'Declare database objects
   Dim objConn As New ADODB.Connection
   Dim objCmd As New ADODB.Command
   Dim objErr As ADODB.Error


We establish a connection to the database using our DSN that was set up in Chapter 6 and then we check the State of the Connection object. For those readers who are using SQL Server authentication, you will need to specify the UID and PWD parameters specifying your SQL Server login and password respectively.

   'Establish a database connection using NT Authentication
   objConn.Open "DSN=Wrox Auto Works"
  
   'Check the state of the connection to ensure we are connected
   If objConn.State = adStateOpen Then


If the State of our connection is open, then we proceed with our code by setting the various properties of the Command object. Here we are setting the ActiveConnection property to our Connection object and the CommandText property to our stored procedure name. The CommandType property gets set to the adCmdStoredProc constant from the CommandTypeEnum enumeration, which specifies that a stored procedure is to be executed.

Once again notice that we have specified the user prefix of the stored procedure that we want to execute, and the prefix will need to be changed to the prefix of the stored procedure that you created. This is not entirely necessary for any stored procedures that we create, or stored procedures that have the dbo prefix. But this is a good habit to get into because it will help when we make the transition from development to test and production, as we will demonstrate in Chapter 16.

      'Set the Command object properties
      Set objCmd.ActiveConnection = objConn
      objCmd.CommandText = "willist.up_parmsel_state_abbreviation"
      objCmd.CommandType = adCmdStoredProc


Since we have two parameters and a return value in our stored procedure, we must append three parameters to the Command object's Parameters collection. A return value is also considered a parameter from an ADO perspective. The first parameter we need to append to the Parameters collection is the return value. If a return value is coded in your stored procedure, it must always be the first parameter in the collection, because of the syntax of a stored procedure call.

You can think of a call to a stored procedure in much the same way as you would a call to an API function that returns a return code. The return code is always set to a value equal to the call of the function.

All other parameters must be appended to the Parameters collection in the order in which they are listed in the stored procedure.

We are using the CreateParameter method of the Command object to create and append our parameters to the Parameters collection. For the return value, we specify a Name for the parameter in the Parameters collection, and then specify the DataType of the parameter. We specify the direction of the parameter, and in this case we have specified the adParamReturnValue constant from the ParameterDirectionEnum enumeration, which indicates that this is a return value:

      'Set the Command object parameters
      objCmd.Parameters.Append _
         objCmd.CreateParameter("RC", adInteger, adParamReturnValue)


The second parameter that we want to append to the Parameters collection is the input parameter. This time we have given the parameter a Name of StateName, and specified the DataTypeadVarChar constant from the DataTypeEnum enumeration. We specify the Direction of the parameter, and in this case it is an input parameter. Since this is a VarChar data type, we must specify the Size of the parameter and here we have specified a size of 15. Last, we specify the Value that we want to pass to the parameter, and for this we have specified our state name:

      objCmd.Parameters.Append _
         objCmd.CreateParameter("StateName", adVarChar, adParamInput, _
            15, "North Carolina")


The last parameter that we must append to the Parameters collection is the output parameter. We give it a Name of Abbreviation, a DataType of adChar, and specify that this is an output parameter by using the adParamOutput constant. Last, we specify the Size of the data type and in this case we have specified 2. Since this is an output parameter, we do not specify a Value:

      objCmd.Parameters.Append _
         objCmd.CreateParameter("Abbreviation", adChar, adParamOutput, 2)


Now that we have set the Command object's properties and appended the parameters to the Parameters collection, it's time to execute the Command object, as shown in the next line of code:

      'Execute the Command object
      objCmd.Execute


After executing the Command object, we need to check the return value from the stored procedure. We set the return value in a parameter called RC and we are checking it in the next line of code. If the return value from the stored procedure is not equal to zero then we know that our stored procedure did not find a match and the output parameter is going to be Null.

If a match was not found for the state name that we passed, then we set the text in the txtAbbreviation textbox to an empty string by specifying two double quotes. We also set the txtReturnCode textbox to the return value returned by the stored procedure.

If a match was found then we set the txtAbbreviation textbox to the value of the Abbreviation parameter. Notice that we are accessing the parameter from the Parameters collection in this statement by specifying a parameter Item in the Parameters collection, which is part of the Command object. We then set the txtReturnCode textbox to the return value returned by the stored procedure using a different syntax. Since the Parameters collection is the default collection of the Command object, we can access the parameters directly from the Command object by just specifying their names.

We could also access the parameters in the Command object by ordinal reference instead of their names. The RC parameter would have an ordinal reference of 0 and the Abbreviation parameter would have an ordinal reference of 2.       If objCmd("RC") <> 0 Then
         txtAbbreviation.Text = ""
         txtReturnCode.Text = objCmd("RC")
      Else
         txtAbbreviation.Text = objCmd.Parameters.Item("Abbreviation")
         txtReturnCode.Text = objCmd.Parameters.Item("RC")
      End If


If the State of the Connection object is not opened, then we enumerate through the Errors collection as we did in the last chapter:

   Else
     
      'Display all errors
      For Each objErr In objConn.Errors
         Debug.Print objErr.Description
      Next
     
   End If


The last part of our code de-references all of our database objects and then our form is displayed:

   'Dereference the database objects
   Set objCmd = Nothing
   objConn.Close
   Set objConn = Nothing
End Sub


After having run your project and obtaining successful results, try misspelling the state name you are passing in the code and see what results you get then, in particular note the Return Code.