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 the DB Access Module for Microsoft SQL Server 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
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;
