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

asked 30 Apr, 08:19

samrae's gravatar image

samrae
2316721
accept rate: 50%


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
permanent link

answered 30 Apr, 08:48

Volker%20Barth's gravatar image

Volker Barth
37.6k343512778
accept rate: 34%

edited 30 Apr, 09:04

1

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.

(30 Apr, 20:39) Chris Keating
Replies hidden

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

(01 May, 07:07) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×185
×28
×17
×5
×1

question asked: 30 Apr, 08:19

question was seen: 105 times

last updated: 01 May, 07:08