Start a new topic
Answered

Execution of a Stored Procedure in a "WHILE" loop

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.


 


Best Answer

That appears to be a bug.  At least, I don't see anything wrong in the script that would cause it, and I also tried it in SQL Server just to be sure.


It seems to be broken in the same way going back to VistaDB 4.2 (the first version from Gibraltar Software) and before--presumably on back to VistaDB 3.0--although I didn't try it in every version along the way.


We'll investigate and try to get this fixed.


    -Rob



Answer

That appears to be a bug.  At least, I don't see anything wrong in the script that would cause it, and I also tried it in SQL Server just to be sure.


It seems to be broken in the same way going back to VistaDB 4.2 (the first version from Gibraltar Software) and before--presumably on back to VistaDB 3.0--although I didn't try it in every version along the way.


We'll investigate and try to get this fixed.


    -Rob


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

Going by the behavior, the problem appears to be in the passing of a varying parameter value as a parameter to a sproc.  The outer sproc was not necessary to reproduce the issue.


So, presumably if the simple code from the inner sproc were substituted into the WHILE loop of the outer code (with appropriate adjustments) then the issue would not occur.  Obviously, this is more practical for some instances than for others.


In more complex cases there might be other ways of restructuring around the issue on a case-by-case basis, but a more general workaround seems elusive.


    -Rob


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.

Login to post a comment