• Article

How to: Execute a Stored Procedure (Using ODBC CALL 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 the IMultipleResults interface is completely released, whichever occurs first.

To process return codes and output parameters

  1. Construct an SQL statement that uses the ODBC CALL escape sequence. The statement should use parameter markers for each input/output and output parameter, and for the procedure return value (if any). For input parameters, you can use the parameter markers, or hard code the values.

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

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

  4. Fill in the DBPARAMS structure.

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

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

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