User's Guide

Part IV. Transact-SQL Compatibility

Chapter 31. Transact-SQL Procedure Language

The previous chapter described SQL Anywhere support for individual SQL statements. This chapter describes the SQL Anywhere support for sets of SQL statements, whether they appear as stored procedures, triggers, or batches. Also discussed in this chapter are individual statements that are typically found within stored procedures, such as variable assignment statements, as well as control-of-flow statements such as IF and WHILE, and compound statements (bracketed by BEGIN and END). Collectively, these aspects of SQL are called the procedure language.

This chapter describes how the two dialects of SQL coexist in SQL Anywhere, and describes the SQL Anywhere functions for automatic translation of Transact-SQL procedures. The chapter also provides reference for the individual statements that make up the Transact-SQL procedure language.

Mixing Transact-SQL and Watcom-SQL dialects

SQL Anywhere supports two SQL dialects: the native Watcom-SQL and Sybase Transact-SQL. There is a large overlap between the two dialects: SELECT * FROM employee is a SQL statement that is part of both Watcom-SQL and Transact-SQL.

Transact-SQL only

There are SQL statements supported by SQL Anywhere that are part of one dialect, but not the other. For example: the following are part of the Transact-SQL dialect only:

• The Transact-SQL control statements IF and WHILE.
• The Transact-SQL EXECUTE statement.
• The Transact-SQL CREATE PROCEDURE and CREATE TRIGGER statements.
• The Transact-SQL BEGIN TRANSACTION statement.
• SQL Statements not separated by semicolons are part of a Transact-SQL procedure or batch.

Watcom-SQL only

The following statements are part of the Watcom-SQL dialect only:

• The Watcom-SQL control statements CASE, IF, LOOP, FOR, and WHILE.
• The Watcom-SQL CALL statement.
• The Watcom-SQL CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER statements.
• SQL Statements separated by semicolons are part of a Watcom-SQL procedure or batch.

Notes

The two dialects cannot be mixed within a procedure, trigger, or batch. That is:

• You can include Transact-SQL-only statements together with statements that are part of both dialects in a batch, procedure, or trigger.
• You can include Watcom-SQL-only statements together with statements that are part of both dialects in a batch, procedure, or trigger.
• You cannot include Transact-SQL-only statements together with Watcom-SQL-only statements in a batch, procedure, or trigger: a syntax error results.