sql anywhere: watcom sql or t-sql

Question

A general question. I am developing for Sybase SQL Anywhere 10. For backwards comptibility reasons, almost all our Stored procedures are written in Transact-SQL. Are there any advantages or disadvantages to using T-SQL instead of the Watcom dialect?

Answer 1

if you ever try to port to SQL Server (or you go for a job on SQL Server), Sybase T-SQL is very close to SQL Server T-SQL. Sybase and MS joined up back in the day, so the core of those languages are very similar.

Answer 2

What KM said - on the other hand, the "Watcom" dialect is much closer to ISO/ANSI-standard SQL, so that dialect is more likely to match to some other products and to appeal to people familiar with SQL standards.

Answer 3

Advantages of TSQL:

• greater compatibility with Sybase ASE and Microsoft SQL Server

Disadvantages of TSQL:

• some statements and functionality are only available in Watcom-SQL procedures. Some examples:
  • greater control over EXECUTE IMMEDIATE behavior in Watcom-SQL
  • LOAD TABLE, UNLOAD TABLE, REORGANIZE (among others) are only available in Watcom-SQL
  • the FOR statement for looping over the results of a query and automatically declaring variables to contain the values is very useful, but not available in TSQL
• error reporting is less consistent since TSQL procedures are assumed to handle their own errors, while Watcom-SQL procedures report errors immediately. Watcom-SQL procedures can contain an EXCEPTION clause to handle errors
• statements are not delimited by semi-colons, so TSQL procedures are more difficult to parse (and read). Syntax errors can sometimes fail to point to the actual location of the error
• no ability to explicitly declare the result set of a procedure
• no support for row-level triggers in TSQL
• event handlers can only be written using Watcom-SQL

The documentation for SQL Anywhere T-SQL compatibility is available online. There are some database options that change behaviour to more closely match what you would expect from Sybase ASE. In addition, there are some functions that can be used to translate from one syntax to another.

Note that if you want to start adding statements in the Watcom dialect into an existing stored procedure, you will need to change the SP so that it is entirely written in the Watcom dialect. You cannot mix syntaxes in a SP, trigger, or batch.