Here's a CALL that behaves as expected, and as documented:

CALL sa_locks ( connection = 2 );

conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier
'ddd16-2',2,'DBA','BASE','DBA','t',,'Row','Transaction','Write',37552128
'ddd16-2',2,'DBA','BASE','DBA','t',,'Position','Transaction','Insert',
'ddd16-2',2,'DBA','BASE','DBA','t',0,'Position','Transaction','Insert',
'ddd16-2',2,'DBA','BASE','DBA','t',,'Schema','Transaction','Shared',
'ddd16-2',2,'DBA','BASE','DBA','t',,'Table','Transaction','Intent',

This one also behaves as expected, but NOT as documented (the docs don't allow for the "parameter =" usage like Syntax 2 of the CALL statement):

SELECT @@VERSION, * FROM sa_locks ( connection = 2 );

@@VERSION,conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Row','Transaction','Write',37552128
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Position','Transaction','Insert',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',0,'Position','Transaction','Insert',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Schema','Transaction','Shared',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Table','Transaction','Intent',

Here is a CALL that fails as expected:

CALL sa_locks ( garbage = 2 );

Could not execute statement.
Parameter 'garbage' not found in procedure 'sa_locks'
SQLCODE=-615, ODBC 3 State="42000"
Line 1, column 1
CALL sa_locks ( garbage = 2 )

This one neither works nor fails as expected; it just ignores the "garbage = 2" and returns all the rows:

SELECT @@VERSION, * FROM sa_locks ( garbage = 2 );

@@VERSION,conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Row','Transaction','Write',37552128
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Position','Transaction','Insert',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',0,'Position','Transaction','Insert',
'16.0.0.1915','ddd16-1',1,'DBA','BASE','DBA','t',,'Schema','Transaction','Shared',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Schema','Transaction','Shared',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Table','Transaction','Intent',

...and that's what's got me scrambling this morning, hunting through all my code for OTHER "FROM procedure ( parameter =" calls that might be silently doing the wrong thing.

Sigh :)

asked 29 Sep '14, 05:57

Breck%20Carter's gravatar image

Breck Carter
27.4k424585838
accept rate: 21%

edited 29 Sep '14, 05:58

Call it a bug...

FWIW, the FROM clause is documented to accept parameters for procedure calls both by position and by name, cf. that paragraph from the cited doc page:

procedure-name [...]
The argument list can be specified by position or by using keyword format. By position, the arguments match up with the corresponding parameter in the parameter list for the procedure (DEFAULT can be used for an optional parameter). By keyword, the arguments are matched up with the named parameters. For information about the supported syntax for named parameters, see Named parameters.

(That seems to be new in the v16 docs, the v12.0.1 docs do not contain that paragraph.)


Personally, I feel save as I seem to always use parameters by position...

(29 Sep '14, 06:10) Volker Barth
Replies hidden

Good catch, Volker! The informal representation used for the FROM syntax in the Help does not seem to allow for parameter-name = expression, but I guess that's what "informal" means: the word "parameter" can mean anything you want it to :)

procedure-name :
[ owner.]procedure-name ( [ parameter, ... ] )
[ WITH ( column-name data-type, ... ) ]
[ [ AS ] correlation-name ]
(29 Sep '14, 19:50) Breck Carter

This stuff is better than a morning cup of coffee. I finally woke up after noticing this to be a feature change.

This change seems to be related to the new "SQL/2011 named parameter syntax" compliance and is documented in the change in the From-clause link Volker noted as well as in a new 'SQL Ref.', 'Language Elements' article on this feature Named Parameters

HTH

permanent link

answered 30 Sep '14, 12:46

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.2k33106
accept rate: 32%

This stuff is better than a morning cup of coffee.

My mileage does vary.

(30 Sep '14, 13:07) Volker Barth

How does the documentation explain why the following does NOT throw a syntax error even though it is CLEARLY incorrect?

SELECT @@VERSION, * FROM sa_locks ( garbage = 2 );

(30 Sep '14, 20:56) Breck Carter

It doesn't completely. It explains why it now works in 16.

It is possible the issue identified in version 12 has been there since the inclusion of stored procedure calls in the from clause back in version 9. That feature is a Std. SQL feature.

All through that patch named parameters were being handled as a T/SQL feature (introduced in version 5.0).

It seems that Version 12 accepts the value as a parameter but is accessing that value positionally; as noted.

(01 Oct '14, 11:12) Nick Elson S...

The plot gets a little thicker than just that too.

This all seems to work with @parm (ie T/SQL) style parameter names.

It only fails with identifier names that don't include the preceding (again t/sql required) @.

If have a repro that works in V12 with '@parms' but fail with unannoted 'parms'. So a further (much more subtle) mixing of dialects.

{just in case you haven't noticed ... still investigating}

(01 Oct '14, 11:40) Nick Elson S...
3

Ignoring the documented differences between the two versions, this specific lack of throwing an error appears to be related to sa_locks( ) being a built-in procedure. Creating a clone version of dba.sa_locks( ) (which just wraps it with a select from the dbo.sa_locks( .... )) causes the expected error to be thrown.

If one wraps sa_locks with a dba procedure of same name and similar definition that will cause the correct -615 error to be thrown.

I am sending that result to product development's attention now.

(01 Oct '14, 14:02) Nick Elson S...
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:

×28

question asked: 29 Sep '14, 05:57

question was seen: 521 times

last updated: 01 Oct '14, 14:02