User's Guide
PART 5. The Adaptive Server Family
CHAPTER 30. Transact-SQL Compatibility
The stored procedure language is that part of SQL used in stored procedures, triggers, and batches.
Adaptive Server Anywhere supports a large part of the Transact-SQL stored procedure language in addition to the Watcom-SQL dialect based on SQL/92.
Transact-SQL stored procedure overviewThe Adaptive Server Anywhere stored procedure language is based on the ISO/ANSI draft standard, which differs from the Transact-SQL dialect in many ways. Many of the concepts and features are similar, but the syntax is different. Adaptive Server Anywhere support for Transact-SQL takes advantage of the similar concepts by providing automatic translation between dialects. However, a procedure must be written in one of the two dialects exclusively, not in a mixture.
There are several aspects to Adaptive Server Anywhere support for Transact-SQL stored procedures:
Passing parameters
Returning result sets
Returning status information
Providing default values for parameters
Control statements
Error handling
Transact-SQL trigger overviewTrigger compatibility requires compatibility of trigger features and of trigger syntax. This section provides an overview of the feature compatibility of Transact-SQL and Adaptive Server Anywhere triggers.
Adaptive Server Enterprise triggers are executed after the triggering statement has completed: they are statement level, after triggers. Adaptive Server Anywhere supports both row level triggers (which execute before or after each row has been modified) and statement level triggers (which execute after the entire statement has been executed).
Row-level triggers are not discussed here, as they are not part of the Transact-SQL compatibility features. For information on row-level triggers in Adaptive Server Anywhere, see Using Procedures, Triggers, and Batches.
The following list describes some features of Transact-SQL triggers that are either not supported or are different in Adaptive Server Anywhere:
Triggers firing other triggers Suppose a trigger carries out an action that would, if carried out directly by a user, fire another trigger. Adaptive Server Anywhere and Adaptive Server Enterprise have slightly different behavior for this case. The default Adaptive Server Enterprise behavior is for triggers to fire other triggers up to a configurable nesting level, which has the default value of 16. The nesting level can be controlled by the Adaptive Server Enterprise option nested triggers. In Adaptive Server Anywhere, triggers fire other triggers without limit unless memory is exhausted.
Triggers firing themselves Suppose a trigger carries out an action that would, if carried out directly by a user, fire the same trigger. Adaptive Server Anywhere and Adaptive Server Enterprise have different behavior for this case.
In Adaptive Server Anywhere, non-Transact-SQL triggers fire themselves recursively, while Transact-SQL dialect triggers do not fire themselves recursively.
The default Adaptive Server Enterprise behavior is that a trigger does not call itself recursively, but you can turn on the self_recursion option to allow triggers to call themselves recursively.
ROLLBACK statement in triggers Within a trigger, Adaptive Server Enterprise permits the ROLLBACK TRANSACTION statement, which rolls back the entire transaction of which the trigger is a part. Adaptive Server Anywhere does not permit ROLLBACK (or ROLLBACK TRANSACTION) statements in triggers. A triggering action and its trigger together form an atomic statement, and Adaptive Server Anywhere does not permit ROLLBACKs within atomic statements.
Transact-SQL batch overviewIn Transact-SQL, a batch is a set of SQL statements submitted together and executed as a group, one after the other. Batches can be stored in command files. The Interactive SQL utility in Adaptive Server Anywhere and the isql utility in Adaptive Server Enterprise provide similar capabilities for executing batches interactively.
The control statements used in procedures can also be used in batches. Adaptive Server Anywhere supports the use of control statements in batches and the Transact-SQL-like use of non-delimited groups of statements terminated with a GO statement to signify the end of a batch.
For batches stored in command files, Adaptive Server Anywhere supports the use of parameters in command files. Adaptive Server Enterprise does not support parameters.
For information on parameters, see PARAMETERS statement.