How to process return codes and output parameters (ODBC)

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 not available to the application until SQLMoreResults returns SQL_NO_DATA.

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, input/output, and output parameter, and for the procedure return value (if any).
2. Call SQLBindParameter for each input, input/output, and output parameter, and for the procedure return value (if any).
3. Execute the statement with SQLExecDirect.
4. Process result sets until SQLFetch or SQLFetchScroll returns SQL_NO_DATA while processing the last result set or until SQLMoreResults returns SQL_NO_DATA. At this point, the variables bound to the return code and output parameters are filled with returned data values.

Examples

This example shows processing a return code and output parameter. Error-checking code was removed to simplify this example.

// CREATE PROCEDURE TestParm @OutParm int OUTPUT AS

// SELECT au_lname FROM pubs.dbo.authors

// SELECT @OutParm = 88

// RETURN 99

#include <stdio.h>

#include <string.h>

#include <windows.h>

#include <sql.h>

#include <sqlext.h>

#include <odbcss.h>

#define MAXBUFLEN    255

SQLHENV        henv = SQL_NULL_HENV;

SQLHDBC        hdbc1 = SQL_NULL_HDBC;    

SQLHSTMT        hstmt1 = SQL_NULL_HSTMT;

int main() {

    RETCODE retcode;

    // SQLBindParameter variables.

    SWORD    sParm1=0, sParm2=1;

    SDWORD    cbParm1=SQL_NTS, cbParm2=SQL_NTS;

     // Allocate the ODBC environment and save handle.

    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

    // Let ODBC know this is an ODBC 3.0 app.

    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,

                            (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

    // Allocate ODBC connection handle and connect.

    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

    retcode = SQLConnect(hdbc1, "MyDSN", SQL_NTS,

                        "sa", SQL_NTS, "MyPassWord", SQL_NTS);

    // Allocate statement handle.

    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);

    // Bind the return code to variable sParm1.

    retcode = SQLBindParameter(hstmt1,1,SQL_PARAM_OUTPUT,SQL_C_SSHORT,

            SQL_INTEGER,0,0,&sParm1,0,&cbParm1);

    // Bind the output parameter to variable sParm2.

    retcode = SQLBindParameter(hstmt1,2,SQL_PARAM_OUTPUT,SQL_C_SSHORT,

                                SQL_INTEGER,0,0,&sParm2,0,&cbParm2);

    // Execute the command.

    retcode = SQLExecDirect(hstmt1, "{? = call TestParm(?)}", SQL_NTS);

    // Show parameters are not filled.

    printf("Before result sets cleared: RetCode = %d, OutParm = %d.\n",

            sParm1, sParm2);

    // Clear any result sets generated.

    while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )

        ;

    // Show parameters are now filled.

    printf("After result sets drained: RetCode = %d, OutParm = %d.\n",

            sParm1, sParm2);

    /* Clean up. */

    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

    SQLDisconnect(hdbc1);

    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

    SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return(0);

}