SqlRetData$

Returns a return-parameter value generated by a stored procedure or a remote stored procedure.

Syntax

SqlRetData$ ( sqlconn%, retnum% )

where

sqlconn%

Is a SQL Server connection. The value of sqlconn% is returned by SqlOpen%.

retnum%

Is the number of the returned value of interest. The first return value is number 1. Values are returned in the same order as the parameters were originally specified in the stored procedure's CREATE PROCEDURE statement. (Note that the order is not necessarily the same as that specified in the remote stored procedure.) When specifying retnum%, non-return parameters are not counted. For example, if the second parameter in a stored procedure is the only return parameter, its retnum% is 1, not 2.

Returns

A string containing the value of the specified retnum%. If retnum% is out of range, SqlRetData$ returns an empty string. To determine whether the data really has a null value (and retnum% is not merely out of range), check for a return value of 0 from SqlRetLen&.

Remarks

The server returns stored procedure information (including any return status and parameter values) immediately after returning all normal results for that stored procedure. Process the normal results, and then call SqlRetData$ after SqlResults% returns NOMORERPCRESULTS (for all stored procedures in a batch except the last one) or NOMORERESULTS (for a single stored procedure, or for the last stored procedure in a batch).

Stored procedures can return values for specified parameters. If the value of one of these parameters is changed in a stored procedure, the new value is returned to the program that called the procedure. This action parallels the pass-by-reference facility available in some programming languages.

To function as a return parameter, a parameter must be declared as such:

• When a stored procedure is created with CREATE PROCEDURE, the parameter must be declared as OUTPUT.
• For a stored procedure executed using the Transact-SQL EXECUTE statement, the parameter must be declared as OUTPUT.
• For a stored procedure executed using DB-Library functions (such as SqlRpcInit%), when the parameter is added using SqlRpcParam%, the status% bitmask must contain the SQLRPCRETURN option.

If a stored procedure is executed using DB-Library functions (such as SqlRpcInit%), the return parameter values are automatically available to the application. If a stored procedure is invoked with an EXECUTE statement, the return parameter values are available only if the command batch containing the EXECUTE statement uses Transact-SQL local variables, not constants, for the return parameters.

See Also

SqlNextRow%, SqlNumRets%, SqlResults%, SqlRetLen&, SqlRetName$, SqlRetType%