Create procedure: bug or behavior change in 17.0.9.4793?

[reimer_pods]

With the recently applied EBF Build 4793 we've encountered a problem when creating procedures in Watcom syntax, that use default values for parameters. Example

create or replace procedure SetValues (in @arg1 integer, in @arg2 char(30) =  null)
begin
  declare @res integer;
  -- do something 
  return @res;
end 

In the last 20 years, that has always been accepted as valid syntax. Using the version mentioned before we're getting an error message

Syntax error at '=' in line 1 (Watcom SQL)

If '=' is replaced with 'default', the procedure gets created sucessfully. If a database, that was created with an earlier SQLA version, is rebuilt the procedure keeps working, but can't be modified without the noted change.

Is this a bug or intentional behaviour change? Any ideas for a workaround? Our data model contains ~ 75 such prodedures, so I'm not enthusiastic about the idea to have to change them all.

[VolkerBarth]

Somehow related to that one? 🙂

Enhancement Request: in SP definition allow "=" instead of "default" for Watcom dialect (SA-17)

[Breck_Carter]

FWIW this bright idea didn't help: SET TEMPORARY OPTION ANSINULL = 'OFF';

[VolkerBarth]

Just to add to Nick's answer there:

AFAIK, the syntax with '=' instead of 'default'

"[...]parameter-name data-type = expression"

has never been officially documented for Watcom-SQL procedures, only for T-SQL.

[reimer_pods]

So I should suppose that this change was a bugfix eliminating an undocumented feature?

Btw, your comment to my other post regarding SQLDIALECT made me look at your sample again. In SC (12.0.1.4436) I can add that function using ISQL and then convert it to Watcom (changes syntax) and to TSQL (ditto). So it seems somewhat inbetween.

[VolkerBarth]

So I should suppose that this change was a bugfix eliminating an undocumented feature?

Well, I'm just another customer so I'm not supposed to be able to answer that one...:)

[Breck_Carter]

FWIW the parser seems to be [cough] flexible when dealing with so-called TSQL features; i.e., some of those features are freely available in Watcom-SQL scripts and they don't change the so-called "dialect".

One disadvantage of "flexible" may be that when changes are made to the parser, flexible features may bend in a different direction.

At this point, the question may be "In order to fix this feature, which other feature would you like to see broken?" 🙂

My personal answer is, "Don't fix it, Reimer will cope" 🙂

[reimer_pods]

"Reimer will cope": I'll do my very best 😉

[VolkerBarth]

Seems particularly reasonable due to the unfortunate lack of comments by the SQL Anywhere developers...

[VolkerBarth]

Well, after Glenn's elaborate answer my statement surely is moot:)

[Former Member · Accepted solution]

Perhaps I'm missing something, but as far as I can tell the syntax above has never been supported by SQL Anywhere.

First a disclaimer - I've gone through more than 10 years of source code changes and tried the above in 12.0.1 and latest v17, but I haven't tried all possible releases and/or EBFs.

Second - as you might imagine, TSQL compatibility is extraordinarily difficult since TSQL is a moving target. In particular the move by Microsoft to include statement delimiters (semicolons) in their TSQL dialect makes Microsoft TSQL much closer to SQL Anywhere's WATCOM dialect in terms of syntax, but moves it further away from ASE's version of TSQL.

Specifically, the CREATE PROCEDURE and CREATE FUNCTION statements in SQL Anywhere have never supported the combination of using IN, OUT, or INOUT with the TSQL default parameter assignment ('='). Transact-SQL has never supported the IN, OUT, INOUT declarations for procedure variables and still does not; see https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-...

However, if one omits the IN declaration for the parameter, as in the enhancement request here (Enhancement Request: in SP definition allow "=" instead of "default" for Watcom dialect (SA-17)) then the TSQL default assignment will work even though the procedure uses WATCOM syntax. The example used in that other post does work for me with current SA17 software and with 12.0.1.

[VolkerBarth · Accepted solution]

@Glenn: Do you plan to have your new account merged with your old and wellknown one? (AFAIK, that should be possible, in contrast to the strange ID situation in the SAP Community...)

[reimer_pods · Accepted solution]

Many thanks, Glen, for your detailed explanation. Now I begin to see the light ...

[VolkerBarth · Accepted solution]

Wow, not only planned, but already done:)