SQLDescribeParam
To describe the parameters of any SQL statement, the SQL Server 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-SQL statement 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 there are comments (/* */) in the Transact-SQL command.
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 Server 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."
To bind an "unlimited" size input parameter, data-at-execution 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.
SQLDescribeParam and Table-Valued Parameters
An application can retrieve table-valued parameter information for a prepared statement with SQLDescribeParam. For more information, see Table-Valued Parameter Metadata for Prepared Statements.
For more information about table-valued parameters in general, see Table-Valued Parameters (ODBC).
SQLDescribeParam Support for Enhanced Date and Time Features
The values returned for date/time types are as follows:
DataTypePtr |
ParameterSizePtr |
DecimalDigitsPtr |
|
|---|---|---|---|
datetime |
SQL_TYPE_TIMESTAMP |
23 |
3 |
smalldatetime |
SQL_TYPE_TIMESTAMP |
16 |
0 |
date |
SQL_TYPE_DATE |
10 |
0 |
time |
SQL_SS_TIME2 |
8, 10..16 |
0..7 |
datetime2 |
SQL_TYPE_TIMESTAMP |
19, 21..27 |
0..7 |
datetimeoffset |
SQL_SS_TIMESTAMPOFFSET |
26, 28..34 |
0..7 |
For more information, see Date/Time Improvements (ODBC).
SQLDescribeParam Support for Large CLR UDTs
SQLDescribeParam supports large CLR user-defined types (UDTs). For more information, see Large CLR User-Defined Types (ODBC).