How to: Execute a Stored Procedure (Using ODBC CALL 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 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 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_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.

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