Parameter Markers

Parameter markers are supported by the ADO, OLE DB, and ODBC-based database APIs. A parameter marker is a question mark (?) placed in the location of an input or output expression in a Transact-SQL statement. Parameter markers allow 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 gets the current data for each stock. The application could:

• Prepare this Transact-SQL statement:

  EXEC GetQuoteProcedure @StockSymbolParameter = ?

    

• Bind an application variable to the parameter marker (?).
• Execute a loop:

  do
  Move the next stock symbol to the bound variable.
  Execute the statement to retrieve the quote for that stock.
  while (there are symbols)

Parameter markers are not limited to being mapped to stored procedure parameters. They can be used anywhere an input expression is used:

UPDATE Employees

SET Title = ?

WHERE EmployeeID = ?

Also, parameter markers can 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 this procedure, which returns an integer return code and a character output parameter:

1. Prepare a statement:

   {? = CALL MyProc (?)}

     

2. Bind the first parameter marker to an integer variable and the second marker to a character array.
3. Execute the statement.
4. 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 placed the return code and output parameter value in the bound variables. Microsoft® SQL Server™ 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:

1. Sets the Command object type to adCmdStoredProc.
2. Sets the command text to just the name of the procedure.
3. Builds a Parameters collection binding all the parameters and return codes to application variables.
4. 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, then 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, then 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 your documentation for OLE DB Provider for SQL Server and SQL Server ODBC driver.

See Also