Processing Results

With Microsoft® SQL Server™ tools, such as SQL Server Query Analyzer or the osql utility, the results of a Transact-SQL statement are either displayed as character text or saved in a text file. SQL Server displays the entire result set at once, rather than fetching the rows one at a time.

The database APIs enable an application to associate, or bind, the columns of a result set with variables in the application. When a result set row is retrieved, the data in the row columns is moved into the bound variables where it can then be used by the application. Once again the data can be retrieved in its native format without being converted to character text.

The database APIs also support cursor processing. This allows the application to retrieve the rows in the result set one at a time, or one block of rows at a time. The application is not forced to retrieve and store the entire result set before processing it.

Data Type Conversions

Programmers building database applications must deal with two levels of data conversion:

• All of the Transact-SQL statements coded in the application must comply with the Transact-SQL data conversion rules when combining objects with operators and functions. The Transact-SQL data conversion and precedence rules also determine the final output data type of these operations.
• The program must comply with the database API data conversion rules when moving data between program variables and database objects such as result set columns, parameters, and return codes.

Transact-SQL supports conversions of data values from one data type to another. For example, this statement converts an integer value into a character string:

CAST ( 123 AS VARCHAR(5) )

The conversions can be explicit using the CAST function, or they can be implicit. For example, if an int column is compared to a char column, the char value is implicitly converted to an int before the comparison is made. The Transact-SQL Reference defines the implicit and explicit conversions allowed by SQL Server. These rules apply only to conversions between Transact-SQL objects.

Another set of rules applies when converting between Transact-SQL objects such as parameters, return codes, and result set columns and their bound program variables. These rules are defined in the documentation for the provider or driver supporting the API. The rules can vary among between the APIs. For example, the SQL Server ODBC driver supports converting the data from a datetime result set column into an ODBC timestamp data structure, and the DB-Library interface does not allow this conversion because it does not support ODBC timestamp data structures.

See Also