Managing Input Parameters, Output Parameters, and Return Values

One of the ways that stored procedures in SQL Server databases differ from stored queries in Jet database files is in their ability to pass back scalar values as output parameters and return values. The next sample illustrates the syntax for concurrently handling an input parameter, an output parameter, and a return value. You have already seen samples demonstrating the use of input parameters, but the samples become more complicated when combined with output parameters and return values.

Recall that an output parameter is just a scalar value passed back from a stored procedure. It could be a datetime, string, number, or binary value, such as an image. A return value is the value passed back from a stored procedure that is the argument of a RETURN statement in the stored procedure. This argument can be a constant or an expression, but it must be an integer.

VBA parameters treat input parameters, output parameters, and return values as parameters. However, a few distinctions between these three entities might not be so obvious at first glance. First, whenever you use a return value along with either an input parameter or an output parameter, you must reserve the first ADO parameter for the return value. Second, the order in which you declare parameters in the stored procedure must match the order in which you create the parameters within a VBA procedure that invokes a command based on the stored procedure. It does not matter if you declare an input parameter before an output parameter. However, it does matter that you use the same order in both the stored procedure and the VBA procedure.

The following T-SQL script shows a SQL Server stored procedure from the text-based Access template for the sample that demonstrates the concurrent use of input and output parameters along with a return value. This stored procedure builds on one presented earlier that extracts the rows from the Orders table for a year that the user specifies at run time. The parameter that designates the year is an input parameter. The @sum_of_freight parameter is an output parameter. Notice that its declaration includes the keyword OUTPUT . T-SQL lets you compute scalar values, such as the one for the @sum_of_freight output parameter with a SELECT statement. In this instance, the statement computes the sum of Freight column values across the year the user specifies in the @year input parameter.

The RETURN statement is always the last one that a stored procedure processes. This is because RETURN transfers control back to the entity that called the current stored procedure. You can insert one or more RETURN statements in an IF ELSE statement or any other branching logic statement to reflect which path a stored procedure took. If a VBA procedure initiated the stored procedure, the stored procedure passes back to the VBA procedure an argument for the RETURN statement. In the following stored procedure, the return value denotes the number of orders for the year in the @year input parameter:

 ALTERPROCEDUREOrders_in_@year_in_out_return --Inputandoutputparameterdeclarations @sum_of_freightmoneyOUTPUT, @yearint     AS     --Developresultset SELECTOrderID,OrderDate,ShipVia,Freight FROMOrders WHEREDATEPART(yyyy,OrderDate)=@year     --Developoutputparameter SELECT@sum_of_freight= SUM(Freight)FROMOrders WHEREDATEPART(yyyy,OrderDate)=@year     --Developreturnvalue RETURN(SELECTCOUNT(OrderID)FROMOrders WHEREDATEPART(yyyy,OrderDate)=@year) 

The following VBA procedure illustrates the syntax for invoking the preceding SQL Server stored procedure. Use the sample as a model for setting ADO parameters that denote a return value, an output parameter, and an input parameter. The VBA code assigns a value to the input parameter, executes the command pointing at the procedure, and captures the output parameter and return value. The sample also returns a subset of the result set from the SELECT statement for the stored procedure.

The sample begins by pointing a Command object at the preceding SQL Server stored procedure. Next, it creates three parameters and appends each one to the parameter collection of the Command object. As indicated previously, the order of parameter declarations is important. The return value goes first. This value recovers the number of order IDs in the result set for the stored procedure. The second parameter points to the @sum_of_freight output parameter. This parameter must go before the input parameter because the stored procedure declares it first. Also notice that the data types match those inside the stored procedure. It's not always necessary that a parameter in a VBA procedure and a stored procedure coincide precisely, but it's a good practice.

After declaring and appending the parameters, the VBA procedure executes the command and assigns its result set to a Recordset object. These two steps make the output parameter and return value available to the VBA procedure. The sample uses these values to display the total freight, total orders, and average freight per order for whichever year the VBA procedure specifies as the input for the stored procedure. Then the sample reexecutes the command to recover the first three records in the result set for the stored procedure's result set. The sample takes this approach to reuse the OpenSubsetOfCmd procedure from the preceding VBA sample. In a production environment with a large result set, you might just reprocess the rst1 procedure that's already available and save your time for reexecuting the stored procedure's SELECT statement.

 SubInOutReturnDemo() Dimcmd1AsADODB.Command Dimprm1AsADODB.Parameter Dimprm2AsADODB.Parameter Dimprm3AsADODB.Parameter Dimrst1AsNewADODB.Recordset     'Instantiatecommandandsetupforusewithstoredprocedure Setcmd1=NewADODB.Command cmd1.ActiveConnection=CurrentProject.Connection cmd1.CommandType=adCmdStoredProc cmd1.CommandText= "Orders_in_@year_in_out_return"     'Setupareturnparameter Setprm1=cmd1.CreateParameter("RETURN",adInteger,_ adParamReturnValue) cmd1.Parameters.Appendprm1     'Setupanoutputparameter Setprm2=cmd1.CreateParameter("@sum_of_freight",_ adCurrency,adParamOutput) cmd1.Parameters.Appendprm2     'Createparameter,assignvalue,andappendtocommand Setprm3=cmd1.CreateParameter("@year",adChar,_ adParamInput,4) prm3.Value= "1996" cmd1.Parameters.Appendprm3     'Executethecommandtorecoverthereturnvalue(cmd1(0)) 'andtheoutputparameter(cmd1(1)) Setrst1=NewADODB.Recordset Setrst1=cmd1.Execute     Debug.Print "Resultsfor " &prm3.Value Debug.Print "TotalFreight= " &FormatCurrency(cmd1(1)) Debug.Print "TotalOrders= " &cmd1(0) Debug.Print "AverageFreight/Order= " &_ FormatCurrency(cmd1(1)/cmd1(0))     'Printsubsetofresultsetfromthecommand Debug.PrintvbCr& "PartiallistofOrdersfor " &prm3.Value OpenSubsetOfCmdcmd1,3     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

Figure 11-22 shows the output for the preceding VBA sample. Its header dynamically portrays the year for which it returns results. The year in the header title comes from the value for prm3 . The next three lines signify the output parameter, the stored procedure's return value, and the ratio of the two. Next the listing shows the first three records in the result set from running the stored procedure to which the Command object in the preceding sample points.

Figure 11.22: The output to the Immediate window for the InOutReturnDemo VBA procedure.

Inserting, Updating, and Deleting Records

Inserting, updating, and deleting records are the three classic database maintenance tasks . There are at least two ways to use stored procedures when inserting records into a table. One method takes advantage of T-SQL to populate one table from all or a subset of another table's records. The source can reside in the same database, a different database, or even a database on a different server. Another way to facilitate inserting records into a table with a stored procedure is to use parameters. This permits you to reuse the stored procedure to input different records into the same table. This reuse model also enables you to update and delete records from a database. Again, one stored procedure can support an indefinite number of updates or deletions.

Inserting Records from a Source Table

Earlier in the section "Creating Sample Tables," we covered the stored procedures CreateAndPopulateShippersTable and CreateAndPopulateOrdersTable . These procedures execute a T-SQL statement to create a table. Then they invoke another stored procedure that copies data from one table to another. When the procedures were initially discussed, it was premature to examine the code for populating the tables. So let's take a look at the syntax now.

The stored procedures for populating the Orders and Shippers tables have the same basic design, except that they refer to different source and destination tables. In the case of the Orders table, the source is the Orders table from the NorthwindCS database and the destination is the Orders table in the Chapter11SQL database ”the sample database for this chapter. In the case of the Shippers table, the source is the Shippers table from NorthwindCS and the destination is the Shippers table in Chapter11SQL. Aside from different field designations, the two stored procedures are identical. One has the name Copy_from_NorthwindCS_Orders , and the other is named Copy_from_NorthwindCS_Shippers .

The sample stored procedure for the Orders table follows . It starts by checking whether the Orders table in the current database has any records. If the number of records is greater than zero, the stored procedure deletes all records from the Orders table. Next, the procedure executes the SET IDENTITY_INSERT statement and sets it to ON. This permits a stored procedure or other collection of T-SQL statements to add records that populate the column in a table with an IDENTITY value. Without this setting, you wouldn't be able to specify that the original IDENTITY values serving as primary key values in the source table populate the primary key in the destination table.

The most important statement in the Copy_from_NorthwindCS_Orders stored procedure is the INSERT statement. This statement has three sets of arguments. Its first argument set is the name of the source table, Orders . This set consists of a single member. The second argument set consists of the column names for the destination table into which to deposit data. And the statement's third argument set comprises the table column names from which to collect data for the destination table. The statement can transfer one or more rows from the source table to the destination table. The syntax for the SELECT statement in the INSERT statement determines which rows transfer from the source to the destination table.

After performing the INSERT statement, the stored procedure restores the SET IDENTITY_INSERT to OFF, its default value. This is critical because only one table in a SQL Server session can have a setting of ON for SET IDENTITY_INSERT . The second table to attempt the assignment of ON in a single SQL Server session generates a run-time error.

 ALTERPROCEDURECopy_from_NorthwindCS_Orders AS     --RemoveanyrecordsalreadypresentinlocalOrderstable IF(SELECTCOUNT(*)FROMOrders)>0 DELETEFROMOrders     --AllowwritingtoIDENTITYcolumn SETIDENTITY_INSERTChapter11SQL..OrdersON     --SpecifytargetandsourcecolumnsforINSERT INSERTINTOOrders(OrderID,OrderDate,Shipvia,Freight) SELECTOrderID,OrderDate,Shipvia,Freight FROMNorthwindCS..Orders     --ResetIDENTITYcolumn SETIDENTITY_INSERTChapter11SQL..OrdersOFF 

Inserting Records from Parameter Values

The next three VBA procedures (shown in the following code) create a SQL Server stored procedure for inserting new records and then use that stored procedure to add a new record. The sample isolates each of these actions in its own batch of T-SQL statements. If the stored procedure for inserting new records exists already, the sample removes the prior version with a third stored procedure. After creating a new stored procedure named Insert_a_new_shipper , you can invoke it by running the VBA procedure named InsertANewShipper . This VBA procedure passes parameter values to and starts the Insert_a_new_shipper stored procedure.

The first procedure, CreateInsert_a_new_shipperProcedure , illustrates how to use VBA to create a SQL Server stored procedure. SQL Server uses the CREATE PROCEDURE statement to create new stored procedures. Then SQL Server syntax calls for additional statements with parameter declarations and T-SQL code that comprise the body of the stored procedure. You can designate these statements with strings in VBA. Next, executing the CREATE PROCEDURE statement with its embedded T-SQL statements saves the batch of embedded T-SQL statements as a stored procedure. The sample uses a Connection object to execute the CREATE PROCEDURE statement. The first procedure concludes with the invocation of the RefreshDatabaseWindow method. The method attempts to update the Database window to show the newly created stored procedure. If the procedure still does not appear, you can manually invoke the View-Refresh command from the Database window.

Before actually attempting to create a new stored procedure, the first procedure calls the Drop_a_procedure VBA procedure. Drop_a_procedure is the last of the three procedures shown in the following sample. The first procedure passes two parameters to Drop_a_procedure . The initial parameter designates the Connection object that points at a database to remove a stored procedure from (if it exists already). The second parameter is the name of the procedure. In the following sample, this is Insert_a_new_shipper . The Drop_a_procedure procedure invokes the DROP PROCEDURE statement only if it detects a stored procedure with the target name in the INFORMATION_SCHEMA.ROUTINES view. This view contains information about the stored procedure and user-defined functions in a database.

InsertANewShipper , the second procedure in the following sample, uses the stored procedure created by the first VBA procedure to add a new record to the Shippers table. InsertANewShipper commences by instantiating a new Command object and pointing it at the stored procedure for inserting new records. Next, the InsertANewShipper procedure specifies and adds parameters to the Command object. Notice that both the @CompanyName and @Phone parameters have a Varchar data type. This kind of data type requires that you specify the maximum number of characters for the parameter. Other parameter data types, such as Numeric , Datetime , and Currency , do not require a length specification. The procedure for inserting a new record concludes by running the Command object. This executes the stored procedure for adding a new record with the values specified for the parameters.

 SubCreateInsert_a_new_shipperProcedure()  Dimstr1AsString Dimcnn1AsADODB.Connection DimProcedureNameAsString     'PointaConnectionobjectatthecurrentproject Setcnn1=CurrentProject.Connection     'Deletetheprocedureifitexistsalready ProcedureName= "Insert_a_new_shipper" Drop_a_procedurecnn1,ProcedureName     'Createtheprocedure str1= "CREATEPROCEDURE " &ProcedureName& " " &vbLf&_  " " &vbLf&_  "@CompanyNamevarchar(40), " &vbLf&_  "@phonevarchar(24) " &vbLf&_  " " &vbLf&_  "AS " &vbLf&_  "INSERTShippersVALUES(@CompanyName,@Phone) " cnn1.Executestr1     'RefreshDatabasewindowtoshownewprocedure RefreshDatabaseWindow     EndSub     SubDrop_a_procedure(cnn1AsADODB.Connection,_ ProcedureNameAsString)  Dimstr1AsString     'Iftheprocedurenameexistsinthe 'INFORMATION_SCHEMA.ROUTINESview,dropit str1= "IFEXISTS(SELECTROUTINE_NAME " &_  "FROMINFORMATION_SCHEMA.ROUTINES " &_  "WHEREROUTINE_NAME='" &ProcedureName& "') " &_  "DROPPROCEDURE " &ProcedureName cnn1.Executestr1     EndSub     SubInsertANewShipper()  Dimcmd1AsADODB.Command Dimprm1AsADODB.Parameter Dimprm2AsADODB.Parameter     'PointaConnectionobjectatthestoredprocedure Setcmd1=NewADODB.Command cmd1.ActiveConnection=CurrentProject.Connection cmd1.CommandType=adCmdStoredProc cmd1.CommandText= "Insert_a_new_shipper"     'Createandappendparameters Setprm1=cmd1.CreateParameter("@CompanyName",adVarChar,_ adParamInput,40) prm1.Value= "CABDelivers" cmd1.Parameters.Appendprm1     Setprm2=cmd1.CreateParameter("@Phone",adVarChar,_ adParamInput,24) prm2.Value= "[phone]" cmd1.Parameters.Appendprm2     'Invokeastoredprocedurebyexecutingacommand cmd1.Execute     EndSub