dbnumrets

Transact-SQL stored procedures can return values for specified “return parameters.” Changes made to the value of a return parameter inside the stored procedure are then available to the program that called the procedure. This is analogous to the “pass by reference” facility available in some programming languages.

For a parameter to function as a return parameter, it must be declared as such within the stored procedure. The execute statement or remote procedure call that calls the stored procedure must also indicate that the parameter should function as a return parameter. In the case of a remote procedure call, it is the dbrpcparam routine that specifies whether a parameter is a return parameter.

When executing a stored procedure, the server returns any parameter values immediately after returning all other results. Therefore, the application can call dbnumrets only after processing the stored procedure’s results by calling dbresults, as well as dbnextrow if appropriate. (Note that a stored procedure can generate several sets of results—one for each select it contains. Before the application can call dbnumrets or any other routines that process return parameters, it must call dbresults and dbnextrow as many times as necessary to process all the results.

If the stored procedure is invoked with a remote procedure call, the return parameter values are automatically available to the application. If, on the other hand, the stored procedure is invoked with an execute statement, the return parameter values are available only if the command batch containing the execute statement uses local variables, not constants, for the return parameters. For more details on return parameters from stored procedures, see the Adaptive Server Enterprise Reference Manual.