Moving Data to Program Variables

Applications accessing Microsoft® SQL Server™ databases through a database API must move data between application variables and:

• Result set columns.

  Applications must move data from the columns of a fetched row in a result set into application variables.

• Return codes.

  Applications must move data from a stored procedure return code into an application variable.

• Parameters.

  Applications must move data between stored procedure parameters and application variables. Parameters can be input or output parameters, so data movement can either be from the variable to the parameter, or from the parameter to the variable.

• Parameter markers.

  ODBC and OLE DB parameter markers are used in SQL statements in place of either input expressions (such as in a WHERE clause search condition) or stored procedure parameters and return codes. Applications must move data from application variables and the expression replaced by the parameter marker. For more information, see Parameter Markers.

Many database APIs use the concept of binding to specify how the data is to be moved between an application variable and the SQL Server object. Database APIs provide functions that an application can call to:

• Determine the data type, size, precision, and scale of a result set column, return code, parameter, or parameter marker. After the application has received this information it can allocate a variable or an array of variables with compatible attributes.
• Bind the result set column, return code, parameter, or parameter marker to a specific variable or array of variables. The binding information typically includes:
  • The address and attributes (data type, size, precision, and scale) of the variable.
  • The name and attributes of the database object.

Data movement typically occurs:

• When a Transact-SQL statement or batch is executed. The OLE DB provider or ODBC driver pulls in the data bound to any input parameters or parameter markers and includes them in the packet sent to SQL Server.
• When a result set row is fetched. The OLE DB provider or ODBC driver moves the data for each column to the bound variables.
• After all the result sets from a stored procedure have been fetched or canceled. The OLE DB provider or ODBC driver moves the data for any output parameters or return codes to their bound variables.

The bound application variables are not required to have the same data type as the SQL Server object to which they are bound. If the data types are different, the OLE DB provider or ODBC driver converts the data when it is moved. The set of conversions supported by each OLE DB provider and ODBC driver are specified in the documentation for the provider or driver.

See Also

Using Variables and Parameters