Microsoft KB Archive/152174

= INF: Output Parameters, Return Codes and the ODBC Driver =

Article ID: 152174

Article Last Modified on 3/16/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q152174



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: kbinfo KB152174

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.