INF: Output Parameters, Return Codes and the ODBC Driver

ID: Q152174

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

SUMMARY

This article discusses how the Microsoft SQL Server ODBC Driver returns stored procedure return codes and output parameters to an ODBC application. SQL Server ODBC drivers from other vendors may do this differently; users should consult the documentation for the driver.

MORE INFORMATION

SQL Server stored procedures can return both output parameters and return codes to an application:


   CREATE PROCEDURE odbcproc @ioparm int OUTPUT AS
   SELECT name FROM sysusers WHERE uid < 2
   SELECT @ioparm = 88
   RETURN 99
   GO

These can be bound to program variables in an ODBC application where the application can reference them. For example, to execute the procedure above using the ODBC CALL syntax and bind the return code and ouput parameters:


   SQLRETURN   rcd;
   DWORD    ProcRet = 0, OParm = 0;
   long     cbProcRet = 0, cbOParm = 0;

   // Bind the return code.
   rcd = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT,
      SQL_C_SLONG, SQL_INTEGER, 0, 0, &ProcRet, 0, &cbProcRet);

   // Bind the output parameter.
   rcd = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT,
      SQL_C_SLONG, SQL_INTEGER, 0, 0, &OParm, 0, &cbOParm;

   // First ? marks the return code,
   // second ? marks the output parameter.
   rcd = (SQLExecDirect(hstmt, "{? = call odbcproc(?)}", SQL_NTS;

SQL Server does not send back the values for the return code or output parameters until the end of all result sets for the procedure. After SQLMoreResults() returns SQL_NO_DATA_FOUND, the program variable ProcRet will hold the return code of 99 and OParm will hold the output parameter value of 88.

Additional query words: Fetch

Keywords :
Version : 6.0 6.5
Platform : WINDOWS
Issue type :