create or replace procedure mytest1 ()
    call logproc (@@procid);

create or replace procedure mytest2 ()
    declare var1 unsigned int;
    set var1 = (select @@procid);
    call logproc (var1);

create or replace procedure logproc (@procid unsigned int)
    message ('call from ' || (select proc_name from sysprocedure where proc_id =@procid));
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, 06:30

Baron's gravatar image

accept rate: 43%

edited 16 Mar, 06:32

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

(16 Mar, 06:45) Baron

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.

(16 Mar, 08:09) Volker Barth
Replies hidden

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

(17 Mar, 11:47) JBSchueler
Replies hidden

I see the same behavior in SQL Anywhere 6.

(17 Mar, 16:00) 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 :)

(17 Mar, 16:32) Breck Carter

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.

(18 Mar, 04:16) Baron
showing 4 of 6 show all flat view

@@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()
    MESSAGE STRING ( @@VERSION, ' mytest1 @@procid = ', @@procid ) TO CONSOLE;
    call logproc (@@procid - 10 );

create procedure logproc ( passed_value unsigned int )
    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;

CALL mytest1 (); mytest1 @@procid = 491 logproc passed_value = 482 logproc @@procid = 492 logproc @@procid -10 = 482 mytest1 @@procid = 217 logproc passed_value = 208 logproc @@procid = 218 logproc @@procid -10 = 208
permanent link

answered 17 Mar, 16:33

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

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, 04:11) Baron
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 16 Mar, 06:30

question was seen: 158 times

last updated: 18 Mar, 04:16