Execute stored procedure (using RPC syntax) and process return codes and output parameters (OLE DB)
New Information - SQL Server 2000 SP3.
Microsoft® SQL Server™ stored procedures can have integer return codes and output parameters. The return codes and output parameters are sent in the last packet from the server and are therefore not available to the application until the rowset is completely released. If the command returns multiple results, output parameter data is available when IMultipleResults::GetResult returns DB_S_NORESULT or the IMultipleResults interface is completely released, whichever occurs first.
To process return codes and output parameters
- Construct an SQL statement that uses the RPC escape sequence.
- Call the ICommandWithParameters::SetParameterInfo method to describe parameters to the provider. Fill in the parameter information in an array of PARAMBINDINFO structures.
- Create a set of bindings (one for each parameter maker) by using an array of DBBINDING structure.
- Create an accessor for the defined parameters by using the IAccessor::CreateAccessor method. CreateAccessor creates an accessor from a set of bindings.
- Fill in the DBPARAMS structure.
- Call the Execute command (in this case, a call to a stored procedure).
- Process the rowset and release it by using the IRowset::Release method.
- Process the return code and output parameter values received from the stored procedure.
The example shows processing a rowset, a return code, and an output parameter. Result sets are not processed. Here is the sample stored procedure used by the application.
USE pubs DROP PROCEDURE myProc GO CREATE PROCEDURE myProc @inparam int, @outparam int OUTPUT AS SELECT title, price FROM titles WHERE royalty > @inparam SELECT @outparam = 100 IF (@outparam > 0) RETURN 999 ELSE RETURN 888 GO
The complete sample code is in this file: InitializeAndEstablishConnection_B.cpp. You can download an archive containing the sample from the SQL Server Downloads page at this Microsoft Web site.
This sample was developed with Microsoft Visual C++® version 6.0, and may expose properties of the Microsoft Foundation Classes.
Security Note When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 cryptoAPI.