passing @@procid to another procedure

[Baron]

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)

[Baron]

Is there maybe anyway to pass parameters by reference / by value?

[VolkerBarth]

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.

[jack_schueler]

As Volker said, you are probably seeing procedure in-lining.

[Breck_Carter]

I see the same behavior in SQL Anywhere 6.

[Breck_Carter]

> 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 🙂

[Baron]

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.

[Breck_Carter]

@@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

[Baron]

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)