create or replace procedure mytest1 () begin call logproc (@@procid); end; create or replace procedure mytest2 () begin declare var1 unsigned int; set var1 = (select @@procid); call logproc (var1); end; create or replace procedure logproc (@procid unsigned int) begin message ('call from ' || (select proc_name from sysprocedure where proc_id =@procid)); end; -------------- -------------- call mytest1 ()--call from logproc call mytest2 ()--call from mytest2 Could someone explain this phenomen, or I see it false!! (sql anywhere 17.0.09, build 4803) asked 16 Mar '22, 06:30 Baron |
@@procid has behaved this way since (at least) SQL Anywhere Version 6. The Help says "@@procid is the stored procedure ID of the currently executing procedure which apparently means the called procedure for @@procid passed as an argument. The following code demonstrates this because @@procid - 10 is evaluated using logproc's @@procid. create procedure mytest1() begin MESSAGE STRING ( @@VERSION, ' mytest1 @@procid = ', @@procid ) TO CONSOLE; call logproc (@@procid - 10 ); end; create procedure logproc ( passed_value unsigned int ) begin MESSAGE STRING ( @@VERSION, ' logproc passed_value = ', passed_value ) TO CONSOLE; MESSAGE STRING ( @@VERSION, ' logproc @@procid = ', @@procid ) TO CONSOLE; MESSAGE STRING ( @@VERSION, ' logproc @@procid -10 = ', @@procid - 10 ) TO CONSOLE; end; CALL mytest1 (); 17.0.9.4882 mytest1 @@procid = 491 17.0.9.4882 logproc passed_value = 482 17.0.9.4882 logproc @@procid = 492 17.0.9.4882 logproc @@procid -10 = 482 6.0.4.3594 mytest1 @@procid = 217 6.0.4.3594 logproc passed_value = 208 6.0.4.3594 logproc @@procid = 218 6.0.4.3594 logproc @@procid -10 = 208 answered 17 Mar '22, 16:33 Breck Carter Thanks for the explanation. I needed to pass the @@procid by value and this could be done only using another variable and assigning the @@procid of the caller procedure and then pass it (by value)
(18 Mar '22, 04:11)
Baron
|
Is there maybe anyway to pass parameters by reference / by value?
I don't know what you are up to, but would the STACK_TRACE() be of help?
AFAIK, simple data types as numbers are generally passed by value, "long strings" will be passed by (immutable) reference for performance reason, see here...
That being said, at least for "simple" functions, their calls might be inlined (i.e. be replaced by the statements building the function's body). I don't know for procedures. If so, that might have an effect here, too.
As Volker said, you are probably seeing procedure in-lining.
I see the same behavior in SQL Anywhere 6.
> procedure in-lining
IMO it is exhibiting the opposite behavior... it's almost as if it is passing an expression to be evaluated dynamically...
...anyway, the truth is stranger :)
What I need is to build a logging procedure (logproc) which logs each call of procedures (procedure_name and parameters).
logproc would be then called at the begining of each/most procedures.
I solved the problem as shown in mytest2 above (using var1), and would be great if you mean that there is a simpler way to do this.