Hello.
I downloaded VISTADB 6.4.2 for evaluation purpose. I created 2 stored procedures "proc0" and "proc3" which has an input parameter. The first executes the second in a "WHILE" loop. However, the result is abnormal. Did I miss something or is this a bug? Here are the 2 stored procedures:

CREATE PROCEDURE [proc0]
AS
BEGIN
declare @i int;
set @i=2;
while(@i<5)
begin
          print 'proc0   i = '+convert(varchar,@i);
          exec proc3 @param1=@i;
          set @i=@i+1;
end
END
CREATE PROCEDURE [proc3]
@param1 Int
AS
BEGIN
       print '  -------- proc3  param1= '+convert(varchar,@param1);
END
And the result of "exec proc0":
proc0   i = 2
  -------- proc3  param1= 2
proc0   i = 3
  -------- proc3  param1= 2
proc0   i = 4
  -------- proc3  param1= 2
SQL Query opened
Rows selected: 0
Rows affected: 0
Execution time: 5 ms

As you can see at the level of "proc3" the parameter does not change and always keeps the same value.

I thank you in advance for your response.


 

Thank you for your answer. I will try to find a workaround while waiting for a new version that will fix this bug.

Thanks for your response Rob. What you propose works, however, the clarity of the code requires us to divide it into several modules (stored procedures or user defined functions). By replacing proc3 with a function (Func3) the problem does not occur. However during the use of this function another problem appeared to me: The key word "RETURN" does not stop the execution of the code which comes after. See this small example:
CREATE PROCEDURE [proc0]
AS
BEGIN
declare @i int,@ret int;
set @i=2;
while(@i<5)
begin
          print 'proc0 i = '+convert(varchar,@i);
          --exec proc3 @param1=@i;
          set @ret=Func3(@i);
          set @i=@i+1;
end
END
And for the UDF:

CREATE FUNCTION [Func3]
( @param1 Int
)
RETURNS Int
AS
BEGIN
      print ' --------Func3 param1= '+convert(varchar,@param1);
      return(0)
      print 'AFTER RETURN '+convert(varchar,@param1);
END
And for the result:

  Open SQL Query: exec proc0
proc0 i = 2
  --------Func3 param1= 2 --> correct
AFTER RETURN 2 --> abnormal
proc0 i = 3
  --------Func3 param1= 3 --> correct
AFTER RETURN 3 --> abnormal
proc0 i = 4
  --------Func3 param1= 4 --> correct
AFTER RETURN 4 --> abnormal
SQL Query opened
Rows selected: 0
Rows affected: 0
Execution time: 9 ms

While in a stored procedure the RETURN keyword stops the execution of the code that comes after.