SET NOEXEC ON in SQL Anywhere

[#1]

What's the equivalent of SET NOEXEC ON in SQL Anywhere? I want to validate a stored procedure without creating it.

[Breck_Carter]

You might try using the SQLDIALECT function which returns 'Watcom-SQL' or 'Transact-SQL' if the SQL is valid, and an error message if not.

Beware, however... SQL Anywhere does not fully validate stored procedures at CREATE time. That's because variables and tables must exist before they are referenced but not necessarily when procedures, triggers and other code blocks are created. This is illustrated by the fifth test case below.

CREATE FUNCTION parse_sql ( @sql LONG VARCHAR )
   RETURNS LONG VARCHAR
BEGIN
   DECLARE @result LONG VARCHAR;
   SET @result = SQLDIALECT ( @sql );
   RETURN ( IF @result IN ( 'Watcom-SQL', 'Transact-SQL' )
               THEN 'OK'
               ELSE @result 
            ENDIF );
END;

SELECT parse_sql ( 'CREATE PROCEDURE p() BEGIN MESSAGE ''Hello, World!''; END' )       AS "1",
       parse_sql ( 'CREATE PROCEDURE p() AS MESSAGE ''Hello, World!''' )               AS "2",
       parse_sql ( 'CREATE PROCEDURE p() AS garbage' )                                 AS "3",
       parse_sql ( 'CREATE PROCEDURE p() BEGIN garbage; END' )                         AS "4",
       parse_sql ( 'CREATE PROCEDURE p() BEGIN SET undeclared = undeclared + 1; END' ) AS "Beware!";

1    2    3                       4                       Beware!
OK   OK   Error at character 24   Error at character 35   OK

[Former Member]

I see. If you drop a table that is referenced in a stored procedure, the only way to see that the procedure is invalid is to run it.

[Breck_Carter]

Yes. Having worked with "strict" databases for many years, not having to create every single thing including temporary tables before creating a stored procedure is a feature, not a bug. However, it does delay the detection of some simple syntax errors (like speling misteaks) until the code is executed. Your tests need to execute every piece of code, and if you have any EVENTs you have to check your console log for error messages (because events don't have any client, that's where the errors have to go). You may be viewing this as a bug, which is why I pointed it out 🙂

[Breck_Carter]

You might want to investigate the SYSOBJECT.status column which exists in Version 10 and later... I don't know if it will help you, I don't know what you're trying to accomplish exactly.