2.8 Stored Procedures

This DBTools.h++ access library includes full support for SQL Server stored procedures, including support for creating and dropping stored procedures, retrieving stored procedure text and parameter definitions, executing stored procedures, processing multiple result sets, processing output parameters, and processing return values. Transact-SQL cursor parameters are not supported by the Microsoft SQL Server ODBC driver at the time of this writing.

2.8.1 Creating Stored Procedures

Stored procedures can be created by calling the createProcedure() method of RWDBDatabase. DBTools.h++ will create the parameter list from a schema you supply, while the body of the stored procedure is supplied by the application. Here is a simple example, which looks up a royalty percentage and returns the royalty schedule based on title and sales level:

RWCString body =
   "declare @total int                                        \n"
   "select @total = titles.total_sales                        \n"
   "    from titles where title_id = @title_id                \n"
       "if ( @total > 0 )                                     \n"
       "  begin                                               \n"
   "    select @total = @total + @newsales                    \n"
   "    select @pc = royalty from roysched                    \n"
   "      where @total >= roysched.lowrange and               \n"
   "            @total <  roysched.highrange                  \n"
   "      and roysched.title_id = @title_id                   \n"
   "  select royalty,lowrange,highrange from roysched         \n"
   "      where roysched.title_id = @title_id                 \n"
   "    return 1                                              \n"
   "  end                                                     \n"
   "else                                                      \n"
   " return 99                                                 ";
RWDBSchema params;
RWDBColumn aParameter;

aParameter.name("@title_id").type(RWDBValue::Int);
aParameter.name("@title_id").nativeType(SQL_INTEGER);
aParameter.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);

aParameter.name("@newsales").type(RWDBValue::Int);
aParameter.name("@newsales").nativeType(SQL_INTEGER);
aParameter.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);

aParameter.name("@pc").type(RWDBValue::Float);
aParameter.name("@pc").nativeType(SQL_FLOAT);
aParameter.paramType(RWDBColumn::outParameter);
params.appendColumn(aParameter);

aDb.createProcedure("royalty", body, params);

2.8.2 Executing Stored Procedures

Microsoft SQL Server stored procedures are quite complete when compared with other databases. An application has the option of processing return parameters, a return status, and/or the results of selects performed by the stored procedure.

Stored procedures are executed using the ODBC CALL syntax. The Microsoft SQL Server ODBC driver is optimized to take advantage of the remote procedure call (RPC) mechanism provided by Microsoft SQL Server.

It is very important that the application perform the correct sequence of processing in order to successfully process each part of a stored procedure as it is returned from the SQL server. Because of the way that Microsoft SQL Server library handles result sets and return values, you must always process the result set before calling RWDBStoredProc::returnValue(). Calling this function before processing the result set causes an incorrect value to be returned.

Here's an example of processing all of the different parts of the stored procedure. We'll use the procedure shown in Section Section 2.8.1, "Creating Stored Procedures."

RWDBStoredProc royalty = aDb.storedProc("royalty");
float percentage;
royalty << myTitleId;                 // from application input
royalty << myNewSales;                // from application input
royalty << &percentage;               // will process on return
RWDBTable royaltyTable = royalty.execute().table();
RWDBReader reader = royaltyTable.reader();
while ( reader() )
{
// save or display the royalties
}
if ( royalty.returnValue().asInt() == 1 )
{
// continue processing with the royalty percentage   
// returned
   if ( percentage > 10.0 )
   ...
}
else
   cout << "invalid title id" << endl;

2.8.3 Instantiating an RWDBStoredProcedure Using Schema Data

The member function:

RWDBDatabase.storedProc(RWCString, RWDBConnection, RWDBSchema)

increases the performance of stored procedures by eliminating the usual parameter fetch required for instantiation. The schema must include the RWDBColumn::ParamType of each parameter in the stored procedure. For example, the code in Section 2.8.1 uses the schema params to create the stored procedure; params could be used to instantiate an RWDBStoredProc.