HandsOn Creating a Stored Procedure that Accepts Parameters

1. Import the Shippers table from the Northwind database.

2. Switch to the Visual Basic Editor window and insert a new module.

3. In the module's Code window, enter the Create_SpWithParam procedure as shown below.

Sub Create_SpWithParam()

Dim conn As ADODB.Connection

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection conn.Execute "CREATE PROCEDURE procEnterData " & _ "(@Company TEXT (40), " & _ "@Tel TEXT (24)) AS " & _

"INSERT INTO Shippers (CompanyName, Phone) " &_ "VALUES (@Company, @Tel);" ExitHere:

If Not conn Is Nothing Then

If conn.State = adStateOpen Then conn.Close End If

Set conn = Nothing Exit Sub ErrorHandler:

If InStr(1, Err.Description, "procEnterData") Then conn.Execute "DROP PROC procEnterData" Resume

Else

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End If End Sub

The stored procedure above will require two values to be entered at run time. The first value is passed by the @Company parameter and the second one by the parameter named @Tel. In this example, the names of the parameters have been preceded with the @ sign for easy migration of the stored procedure into the SQL Server environment. If you omit the @ sign, the procedure will still

Programming with the Jet Data Definition Language execute correctly in Microsoft Access. If the procedure already exists, it will be dropped using the DROP PROC statement.

Similar to views, stored procedures appear in the Database window in the Queries view. Because we used the SQL INSERT INTO statement, Microsoft Office Access is treating this stored procedure as a parameterized Append query.

To execute the stored procedure named procEnterData created by the VBA procedure in Hands-On 23-4, double-click the stored procedure name in the Queries pane of the Access Database window. Figures 23-2 through 23-7 outline the process of running this stored procedure.

Figure 23-2: When you double-click a stored procedure name in the Queries pane of the Access Database window, Access displays this message when the stored procedure expects parameters and its SQL statement attempts to insert data into a table.

Figure 23-2: When you double-click a stored procedure name in the Queries pane of the Access Database window, Access displays this message when the stored procedure expects parameters and its SQL statement attempts to insert data into a table.

Figure 23-3: Since the stored procedure expects some input, you are being prompted for the first parameter value.

Figure 23-3: Since the stored procedure expects some input, you are being prompted for the first parameter value.

Figure 23-4: Here you are being prompted to enter the phone number for the second stored procedure parameter.

Figure 23-4: Here you are being prompted to enter the phone number for the second stored procedure parameter.

Figure 23-5: Once all input has been gathered via the parameters, Access informs you about the action that is to be performed. Click Yes to execute the stored procedure or No to cancel.

Si Shippers ; Table

Shipper ID | Company Name \

Phone

{] Speedy Express

(503) 555-9631

2 United Package

(503) 555-3160

3 Federal Shipping

(503) 555-9331

4 Orient Express

B00-234-9B9B

ur

\uto Number)

| Reccrd: [_HJ | i | ► ][►! ]>*] of 4

Figure 23-6: Once you click Yes, Access displays the result of the operation. Notice that a new record (Orient Express) was added to the Shippers table.

Part III

P proclntcrDala : Append Query f-~|fPl|x|

Figure 23-7: You can examine the contents of the stored procedure in the Design view (choose View | Design View). This example displays the Design view of the Append query. Other stored procedures that you create may be presented as different Action queries.

P proclntcrDala : Append Query f-~|fPl|x|

Figure 23-8: You can examine the SQL statements used by Access to execute your stored procedure by switching to SQL view (choose View | SQL View).

0 0

Post a comment