Last modified 11 years ago Last modified on 01/09/2008 01:31:08 PM

Stored Procedures: cross-dbms compatibility

LSST Database

Flow control and procedural language is not part of the SQL standard, at least not the Entry Level SQL-92 that most DBMSs support (which is very close to the Core SQL-99). However, for convenience they all support some version of the IF-THEN-ELSE, CASE and WHILE-DO syntax. The problem is that each syntax is slightly different, so it's not possible to write one version of SQL code that can run on all platforms.

For example (using MySQL and SQL Server to illustrate), SQL Server has the

	IF <condition>

syntax (note no THEN after IF), whereas MySQL 5.0 has the THEN but no BEGIN-END.

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]

Similarly, while both MySQL and SQL Server have a WHILE statement, SQL Server uses WHILE ... BEGIN ... END, wheras MySQL has WHILE ... DO ... END WHILE.

The CASE statement syntax looks like it's the same in both.

In MySQL, if you prefix a variable name with '@' it causes the name to refer to a global-per-session variable. In SQL Server, it looks like all variable names have to be prefixed with '@' (and presumably there is some other way to differentiate scope).