INF: Output Parameters, Return Codes and the ODBC Driver (152174)

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.