Share via


SQLDescribeParam

To describe the parameters of any SQL statement, the SQL Native Client ODBC driver builds and executes a Transact-SQL SELECT statement when SQLDescribeParam is called on a prepared ODBC statement handle. The driver uses the SET FMTONLY statement when executing the query. The metadata of the result set determines the characteristics of the parameters in the prepared statement.

Consider this ODBC SQL statement:

INSERT INTO Shippers (ShipperID, CompanyName, Phone) VALUES (?, ?, ?)

On a call to SQLDescribeParam, this ODBC SQL statement causes the driver to execute the following Transact-SQL statements:

SET FMTONLY ON
SELECT ShipperID, CompanyName, Phone FROM Shippers
SET FMTONLY OFF

SQLDescribeParam can, therefore, return any error code that SQLExecute or SQLExecDirect might return.

Further, the driver does not support calling SQLDescribeParam in the following situations:

  • After SQLExecDirect for any Transact-SQL UPDATE or DELETE statements containing the FROM clause.
  • For any ODBC or Transact-SQL statement containing a parameter in a HAVING clause, or compared to the result of a SUM function.
  • For any ODBC or Transact-SQLstatement depending on a subquery containing parameters.
  • For ODBC SQL statements containing parameter markers in both expressions of a comparison, like, or quantified predicate.
  • For any queries where one of the parameters is a parameter to a function.

When processing a batch of Transact-SQL statements, the driver also does not support calling SQLDescribeParam for parameter markers in statements after the first statement in the batch.

When describing the parameters of prepared stored procedures, SQLDescribeParam uses the system stored procedure sp_sproc_columns to retrieve parameter characteristics. sp_sproc_columns can report data for stored procedures within the current user database. Preparing a fully qualified stored procedure name allows SQLDescribeParam to execute across databases. For example, the system stored procedure sp_who can be prepared and executed in any database as:

SQLPrepare(hstmt, "{call sp_who(?)}", SQL_NTS);

Executing SQLDescribeParam after successful preparation returns an empty row set when connected to any database but master. The same call, prepared as follows, causes SQLDescribeParam to succeed regardless of the current user database:

SQLPrepare(hstmt, "{call master..sp_who(?)}", SQL_NTS);

For large value data types, the value returned in DataTypePtr is SQL_VARCHAR, SQL_VARBINARY, or SQL_NVARCHAR. To indicate that the size of the large value data type parameter is "unlimited," the SQL Native Client ODBC driver sets ParameterSizePtr to 0. Actual size values are returned for standard varchar parameters.

Note

If the parameter has already been bound with a maximum size for the SQL_VARCHAR, SQL_VARBINARY, or SQL_WVARCHAR parameters, the bound size of the parameter is returned, not "unlimited."

In order to bind an "unlimited" size input parameter DAE must be used. It is not possible to bind an "unlimited" size output parameter (there is no method for streaming data from an output parameter, like SQLGetData does for result sets).

For output parameters, a buffer must be bound and if the value is too large, the buffer is filled and a SQL_SUCCESS_WITH_INFO message and is returned along with the "string data; right truncation" warning. The data that was truncated is then discarded.

See Also

Concepts

ODBC API Implementation Details

Other Resources

SQLDescribeParam Function

Help and Information

Getting SQL Server 2005 Assistance