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

  • Article

New: 14 April 2006

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

  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 structure.

  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 AdventureWorks
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.

ms403291.security(en-US,SQL.90).gifSecurity 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.

See Also

Concepts

Processing Results (OLE DB)

Help and Information

Getting SQL Server 2005 Assistance