A number of error handling techniques are available to the Transact-SQL programmer, including the ability to capture return status from stored procedures, define customized return values from stored procedures, pass parameters from a procedure to its caller, and get reports from global variables such as @@error. The raiserror and print statements, in combination with control-of-flow language, can direct error messages to the user of a Transact-SQL application. Developers can localize print and raiserror to use different languages.
set options customize the display of results, show processing statistics, and provide other diagnostic aids for debugging your Transact-SQL programs. All set options except showplan and char_convert take effect immediately.
The following paragraphs list the available set options. For more information, see the Reference Manual: Commands.
parseonly, noexec, prefetch, showplan, rowcount, nocount, and tablecount control the way a query is executed. The statistics options display performance statistics after each query. flushmessage determines when Adaptive Server returns messages to the user. See Chapter 2, “Using Interactive isql from the Command Line, in the Utility Guide.
arithabort determines whether Adaptive Server aborts queries with arithmetic overflow and numeric truncation errors. arithignore determines whether Adaptive Server prints a warning message if a query results in an arithmetic overflow. For more information, see “Arithmetic errors”.
offsets and procid are used in DB-Library™ to interpret results from Adaptive Server.
datefirst, dateformat, and language affect date functions, date order, and message display.
char_convert controls character set conversion between Adaptive Server and a client.
textsize controls the size of text or image data returned with a select statement. See “Text functions used for text, unitext, and image data”.
cursor rows and close on endtran affect the way Adaptive Server handles cursors. See “Fetching data rows using cursors”.
identity_insert allows or prohibits inserts that affect a table’s identity column. See “Gaps from other causes”.
chained and transaction isolation level control how Adaptive Server handles transactions. See “Selecting the transaction mode and isolation level”.
self_recursion allows Adaptive Server to handle triggers that cause themselves to fire. See “Trigger self-recursion”.
ansinull, ansi_permissions, and fipsflagger control whether Adaptive Server flags the use of nonstandard SQL. string_rtruncation controls whether Adaptive Server raises an exception error when truncating a char or nchar string. See “Compliance to ANSI standards”.
quoted_identifier controls whether Adaptive Server treats character strings enclosed in double quotes as identifiers. See “Delimited identifiers” for more information.
role controls the roles granted to you. For information about roles, see Chapter 13, “Getting Started With Security Administration,” in Adaptive in the System Administration Guide: Volume 1.
