• Article

How to: Execute a Stored Procedure (Using RPC Syntax) and Process Return Codes and Output Parameters (OLE DB)

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 when the IMultipleResults interface is completely released, whichever occurs first.

To process return codes and output parameters

  1. Construct an SQL statement that uses the RPC escape sequence.

  2. Call the ICommandWithParameters::SetParameterInfo method to describe parameters to the provider. Fill in the parameter information in an array of PARAMBINDINFO structures.

  3. Create a set of bindings (one for each parameter maker) by using an array of DBBINDING structures.

  4. Create an accessor for the defined parameters by using the IAccessor::CreateAccessor method. CreateAccessor creates an accessor from a set of bindings.

  5. Fill in the DBPARAMS structure.

  6. Call the Execute command (in this case, a call to a stored procedure).

  7. Process the rowset and release it by using the IRowset::Release method.

  8. Process the return code and output parameter values received from the stored procedure.

Example

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 AdventureWorks2008R2;
GO
DROP PROCEDURE myProc;
GO

CREATE PROCEDURE myProc 
    @inparam int,
    @outparam int OUTPUT

AS
SELECT Color, ListPrice 
FROM Production.Product WHERE Size > @inparam;
SELECT @outparam = 100

IF  (@outparam > 0)
    RETURN 999
ELSE
    RETURN 888;
GO

The complete sample code is in the file InitializeAndEstablishConnection_B.cpp. You can download an archive containing the sample from the SQL Server Downloads page on MSDN.

This sample was developed using Microsoft Visual C++ 2005.

Security noteSecurity 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 Crypto API.