• Article

Moving Data to Program Variables

Applications that access SQL Server databases by using a database API must move data between application variables and the following:

  • 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. Therefore, data movement can be either 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 instead 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 (Database Engine).

Binding

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 perform the following:

  • 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 following:

    • The address and attributes (data type, size, precision, and scale) of the variable.

    • The name and attributes of the database object.

Data is typically moved when one of the following actions occurs:

  • 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 an instance of SQL Server.

  • A result set row is fetched.

    The OLE DB provider or ODBC driver moves the data for each column to the bound variables.

  • 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.