WORKING WITH STORED PROCEDURES

If you've ever written a VBA function or event procedure, you understand the concept of creating a block of code that achieves a specific result and may include flow-control statements and accept or return argument values. This concept describes SQL Server stored procedures, which are named, saved code routines that perform operations on server data. They're written in the Transact-SQL language (T-SQL for short). Here are the primary attributes of a stored procedure:

• It's compiled at the time it's saved on the server so that it executes quickly.

• Because it's a database object, security permissions/restrictions can be applied to it by using SQL Server's security model.

• It can accept parameters and can return values—variables and recordsets—to the user interface or calling routine.

• It can include If..Else logic to control the code flow conditionally.

• It can contain multiple SQL statements that operate on data.

• It can call other stored procedures.

Neither the Access interface nor the SQL Server compiler are very helpful or forgiving when you write stored procedure code. You won't find the delightful Auto List Members or Auto Quick Info features of the Visual Basic Editor in the Stored Procedure design window. You won't think that syntax errors generated by the compiler are particularly helpful. You also won't be able to use the Windows Undo feature! And you will dislike the lack of debugging tools for stored procedures.

So, how do you write a stored procedure? The same way you eat an elephant—one bite at a time. You will have the best success at server-side coding if you break your stored procedure development into small tasks, and then do your development one task at a time. Create your primary stored procedure, define the parameters, and outline the structure. Then, create another stored procedure to serve as your temporary workspace. Write code in the temporary workspace, compiling and checking syntax frequently. When you've completed the coding on the task at hand and tested your work, move the code to the real stored procedure, clear out your workspace, and continue coding the next task. In this way, you can build a larger stored procedure a few lines at a time.

Creating Stored Procedures

To create a stored procedure, follow these steps:

If the stored procedure returns a recordset, the recordset can be displayed as a datasheet in Access and used as the record source for forms and reports. If you want a stored procedure to return records, the Select keyword must appear directly after Create Procedure "procname" As. Listing 25.3 shows a simple stored procedure that returns a recordset.

Listing 25.3. Returning a Recordset from a Stored Procedure

Create Procedure prcProdList
AS
  SELECT Products.ProductName, Products.UnitPrice
  FROM Products
  ORDER BY Products.ProductName ASC

If you want to pass information to a stored procedure or receive values back, you create parameters on the procedure. A stored procedure can have many parameters (analogous to arguments in a VBA function). With parameters, a stored procedure can pass values back to your ADO code or to a calling stored procedure.

To define parameters at the top of the procedure, prefix each one with the @ character (as in @prmProdName) and define the data type. Refer to Figure 26.18 to see how Access inserts placeholders for parameters in the stored procedure code template.

An input parameter can be used in stored procedure code anywhere a literal value might appear; it can't be used to replace code syntax or an object name (such as a table name). Listing 25.4 shows a simple stored procedure that accepts a parameter, uses it in the Where clause, and displays a recordset with the price for a specified product.

Listing 25.4. A Simple Stored Procedure with an Input Parameter

Create Procedure prcProdPrice
  @prmProdName varchar(40)
AS
  SELECT Products.ProductName, Products.UnitPrice
  FROM Products
  WHERE Products.ProductName = @prmProdName

When you run a stored procedure that has one or more parameters from the Database window, Access displays the Enter Parameter Value dialog once for each parameter (see Figure 25.19). When running a stored procedure from code or a form, the parameter values must be passed in as form properties or ADO properties, respectively.

A stored procedure can return values instead of, or in addition to, recordsets. These values, known as output parameters, are declared with the Output keyword. Like VBA functions, stored procedures can also have one return value; your code usually sets this value to indicate an error condition or successful completion.

The stored procedure in Listing 25.5 contains one input parameter and one output parameter and sets the return value. The procedure accepts a product name as the input parameter, returns the price for the specified product as the output parameter, and sets the return value to 0 if successful or -1 if the product wasn't found.

Listing 25.5. A Stored Procedure with an Output Parameter and Return Value

Create Procedure prcProdPrice
  @prmProdName varchar(40),
  @rprmProdPrice money OUTPUT
AS
  SELECT @rprmProdPrice = Products.UnitPrice
  FROM Products
  WHERE Products.ProductName = @prmProdName
  IF (@rprmProdPrice > 0)
    RETURN 0
  ELSE
    RETURN –1

To use the stored procedure in Listing 25.5, some ADO code must be written to set and test the parameters, because the procedure doesn't return records to the user interface. Listing 25.6 shows how such code would look.

Listing 25.6. Setting and Testing a Stored Procedure's Parameters

Private Sub PrcTest()

  Dim cmd     As New ADODB.Command
  Dim cnn     As New ADODB.Connection
  Dim fld     As ADODB.Field
  Dim prmIn   As Parameter
  Dim prmOut  As Parameter
  Dim prmRet  As Parameter
  Dim rst     As New ADODB.Recordset
  Dim varProd As Variant

  varProd = Trim(InputBox("Enter product name:"))

  cnn.ConnectionString = CurrentProject.BaseConnectionString
  cnn.Open
  cmd.ActiveConnection = cnn
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "prcProdPrice"

  ' Bind parameters; this must occur in the order they are declared
  Set prmRet = cmd.CreateParameter("Return", adInteger, _
     adParamReturnValue)
  cmd.Parameters.Append prmRet
  Set prmIn = cmd.CreateParameter("Input", adVarChar, adParamInput, 40)
  cmd.Parameters.Append prmIn
  prmIn.Value = varProd
  Set prmOut = cmd.CreateParameter("Output", adCurrency, adParamOutput)
  cmd.Parameters.Append prmOut
  cmd.Execute

  Debug.Print "Return value: " & cmd.Parameters(0)
  Debug.Print "Product price: " & cmd.Parameters(2)

  cnn.Close

End Sub

Comparing Stored Procedure and Access Syntax

Access and SQL Server don't use identical syntax elements (such as operators) to achieve the same result. You have to learn the differences between Access and SQL Server syntax to work with stored procedures. Table 25.6 shows the difference in certain characters between a Jet-based application and an Access Project.

The Transact-SQL language that you use to write stored procedures has many functions similar in purpose to functions you may already use or know about in VBA, but with a different syntax. Table 25.7 shows Transact-SQL functions that you can use in stored procedures and their corresponding VBA function.