Processing Results
With Microsoft® SQL Server™ tools, such as SQL 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.
When an application executes a Transact-SQL statement that returns a relational result set, 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 of relational result sets. 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.
When an application executes an XPath query or Transact-SQL statement that returns an XML document, the document is returned as a stream object. For more information, see XML and Internet Support Overview.
Data Type Conversions
Programmers building database applications that use relational result sets must handle 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 conversion 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 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, but the DB-Library interface does not allow this conversion because it does not support ODBC timestamp data structures.