Parameter Markers (Database Engine)
Parameter markers are supported by the ADO, OLE DB, and ODBC-based database APIs. A parameter marker is a question mark (?) put in the location of an input or output expression in a Transact-SQL statement. Parameter markers allow for an application to optimize the case where the same Transact-SQL statement is executed several times with different values for the input and output expressions.
For example, a user may have given an application five different stock symbols and the application has to call a stored procedure that obtains the current data for each stock. The application could do the following:
Prepare this Transact-SQL statement:
EXEC GetQuoteProcedure @StockSymbolParameter = ?Bind an application variable to the parameter marker (?).
Execute a loop:
- Move the next stock symbol to the bound variable.
- Execute the statement to retrieve the quote for that stock.
Parameter markers are not limited to being mapped to stored procedure parameters. Parameter markers can be used anywhere an input expression is used, for example:
UPDATE HumanResources.Employees
SET Title = ?
WHERE EmployeeID = ?
Parameter markers can also be used to map stored procedure output parameters and return codes. When the application executes a stored procedure, the OLE DB provider or ODBC driver moves the data values from any output parameters or return codes into the variables bound to the parameter's markers.
For example, an application can execute the following procedure. This procedure returns an integer return code and a character output parameter.
Prepare a statement:
{? = CALL MyProc (?)}Bind the first parameter marker to an integer variable and the second marker to a character array.
Execute the statement.
Fetch or cancel all the result sets returned by the stored procedure.
At this point, the Microsoft OLE DB Provider for SQL Server or SQL Server ODBC driver will have put the return code and output parameter value in the bound variables. Microsoft SQL Server 2005 returns output parameter and return code values in the last packet it returns to the client. Therefore, the application must process or cancel all result sets returned by the stored procedure before it has access to the return code and output parameter values.
The ADO API has a variation on this process for executing stored procedures. An ADO application performs the following:
- Sets the Command object type to adCmdStoredProc.
- Sets the command text to just the name of the procedure.
- Builds a Parameters collection binding all the parameters and return codes to application variables.
- Executes the Command object.
Parameter markers are associated with a database object that has a specific data type. If the application binds a parameter marker to a variable whose data type differs from that of the associated database object, the OLE DB provider or ODBC driver must convert the data. For example, if an application binds an integer return code to a character array, the OLE DB provider or ODBC driver must convert the return code integer data to a character string. For information about the data type conversions that are supported, see the documentation for OLE DB Provider for SQL Server and SQL Server ODBC driver.
See Also
Other Resources
Running Stored Procedures (OLE DB)
Command Parameters
Using Statement Parameters