MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server

Running Stored Procedures (OLE DB)

A stored procedure is an executable object stored in a database. Microsoft® SQL Server™ supports two types of stored procedures:

Stored procedures—One or more SQL statements that have been precompiled into a single executable procedure.
Extended stored procedures—C or Microsoft Visual C++® DLLs written to the SQL Server Open Data Services API for extended stored procedures. The Open Data Services API extends the capabilities of stored procedures to include C or C++ code.

When executing statements, calling a stored procedure on the data source (instead of executing or preparing a statement in the client application directly) can provide the following advantages:

Higher performance
Reduced network overhead
Better consistency
Better accuracy
Added functionality

The OLE DB provider supports the following mechanisms that SQL Server stored procedures use to return data:

Every SELECT statement in the procedure generates a result set.
The procedure can return data through output parameters.
The procedure can have an integer return code.

The application must be able to handle all of these outputs from stored procedures.

Different OLE DB providers return output parameters and return values at different times during result processing. In the case of SQLOLEDB, the output parameters and return codes are not supplied until after the consumer has retrieved or canceled the result sets returned by the stored procedure. The return codes and the output parameters are returned in the last TDS packet from the server.

Providers use the DBPROP_OUTPUTPARAMETERAVAILABILITY property to report when it returns output parameters and return values. This property is in the DBPROPSET_DATASOURCEINFO property set.

SQLOLEDB sets the DBPROP_OUTPUTPARAMETERAVAILABILITY property to DBPROPVAL_OA_ATROWRELEASE to indicate that return codes and output parameters are not returned until the result set is processed or released.