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, 06:30

Baron's gravatar image

Baron
1.7k112125144
accept rate: 48%

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()
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
permanent link

answered 17 Mar, 16:33

Breck%20Carter's gravatar image

Breck Carter
32.5k5377231050
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

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:

×122
×23

question asked: 16 Mar, 06:30

question was seen: 318 times

last updated: 18 Mar, 04:16