Question Re: SQL ANYWHERE 17: Errors CREATE-ing multiple procedures/functions t-sql in Technology Q&A

SQL ANYWHERE 17: Errors CREATE-ing multiple procedures/functions t-sql

former_SQLA_member1694902 — Fri, 30 Apr 2021 08:20:00 GMT

If I run the following statement:

//create a watcom function
CREATE OR REPLACE FUNCTION "DBA"."TEST1" (@val INT)
RETURNS INTEGER
BEGIN

    IF (@val = 1) THEN
        SET @val = 2;
    ENDIF;

    RETURN @Val;
END;
//create a t-sql function
CREATE OR REPLACE FUNCTION "DBA"."TEST2" (@val INT)
RETURNS INTEGER
AS
BEGIN

    IF @val = 1 BEGIN
        SET @val = 2;
    END
    RETURN @Val;
END;

It runs fine, no errors.

But if I run this:

//create a t-sql function
CREATE OR REPLACE FUNCTION "DBA"."TEST1" (@val INT)
RETURNS INTEGER
AS
BEGIN

    IF (@val = 1) BEGIN
        SET @val = 2;
    END

    RETURN @Val;
END;
//create a t-sql function
CREATE OR REPLACE FUNCTION "DBA"."TEST2" (@val INT)
RETURNS INTEGER
AS
BEGIN

    IF @val = 1 BEGIN
        SET @val = 2;
    END
    RETURN @Val;
END;

It throws:

Could not execute statement.
Syntax error near 'FUNCTION' on line 14 (Transact-SQL)
SQLCODE=-131, ODBC 3 State="42000"
Line 15

Line 14 is where the second function declaration starts, so it's like it's reading off the end of the first function because of the BEGIN/END statement in the IF clause.

Am I writing bad code and/or is there something I'm missing? I write most of my procedure/functions in t-sql and use IF/ELSE quite a bit. If run the CREATE FUNCTION statements individually they don't throw errors.

Any help would be appreciated.

SQL Anywhere 17 17.0.10.5963

Re: SQL ANYWHERE 17: Errors CREATE-ing multiple procedures/functions t-sql

VolkerBarth — Fri, 30 Apr 2021 08:48:42 GMT

Transact-SQL (at least the ASE-compatible variant used within SQL Anywhere) does not use the semicolon as statement delimiter, and a batch of SQL statements must either by completely written in Watcom SQL or TSQL, see here:

SQL statements not separated by semicolons are part of a Transact-SQL procedure or batch.

So just omit the semicolons altogether and possilby add a "go" between different DDL statements.

Besides that, the T-SQL function does seem to use a mix of both dialects, at least the SQLDIALECT function does return an error when called with the T-SQL code. FWIW, the TRANSACTSQL function called with the first function does return:

create or replace function DBA.TEST1( @val integer ) 
returns integer as
begin
  if(@val = 1)
    set @val = 2
  return @Val
end

Re: SQL ANYWHERE 17: Errors CREATE-ing multiple procedures/functions t-sql

chris_keating — Fri, 30 Apr 2021 20:39:39 GMT

This would been permitted in versions prior to SQLA17 albeit it is mixing of the Watcom and TSQL dialects. SQLA 17 added support for semicolon delimiters in TSQL procedures and batches but in doing so a more strict adherence to TSQL syntax was needed -- meaning mixed dialect statements such as that reported here now fail. In TSQL, you cannot create two procedures (or functions) in the same batch. A semicolon in TSQL is a statement separator not a batch separator. The correct approach is to either use GO which is a batch separator or execute the CREATE FUNCTIONs separately.

Re: SQL ANYWHERE 17: Errors CREATE-ing multiple procedures/functions t-sql

VolkerBarth — Sat, 01 May 2021 07:07:09 GMT

Thanks for the clarification, and yes, I had incidentally run my small test with v16, not with v17.