|
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 |
|
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:
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 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 '21, 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 '21, 07:07)
Volker Barth
|
