Processing Stored Procedure Results


System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

SQL Server stored procedures have four mechanisms used to return data:

  • Each SELECT statement in the procedure generates a result set.

  • The procedure can return data through output parameters.

  • A cursor output parameter can pass back a Transact-SQL server cursor.

  • The procedure can have an integer return code.

Applications must be able to handle all these outputs from stored procedures. The CALL or EXECUTE statement should include parameter markers for the return code and output parameters. Use SQLBindParameter to bind them all as output parameters and the SQL Server Native Client ODBC driver will transfer the output values to the bound variables. Output parameters and return codes are the last items returned to the client by SQL Server; they are not returned to the application until SQLMoreResults returns SQL_NO_DATA.

ODBC does not support binding Transact-SQL cursor parameters. Because all output parameters must be bound before executing a procedure, any Transact-SQL stored procedure that contains an output cursor parameter cannot be called by ODBC applications.

Running Stored Procedures

Community Additions