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
R
Rob Parker
said
11 months ago
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.
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
R
Rendou Coziza
said
10 months ago
Thank you for your answer. I will try to find a workaround while waiting for a new version that will fix this bug.
Rob Parker
said
10 months ago
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
R
Rendou Coziza
said
10 months ago
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.
Rendou Coziza
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.
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
- Oldest First
- Popular
- Newest First
Sorted by Oldest FirstRob Parker
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
Rendou Coziza
Thank you for your answer. I will try to find a workaround while waiting for a new version that will fix this bug.
Rob Parker
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
Rendou Coziza
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.
-
Corrupt database file
-
Copy Record From One Table to Another
-
VDB 4.2 - delete from [Table] where [Column] in(Value List)
-
Cannot see topics I have added
-
Tool for Compare 2 databases
-
How to create a computed field ?
-
c#.net Input Byte[] into image
-
VDB 4.2 DDA SetScope...
-
Slow attach to database file + error
-
Refreshing data from a bound grid
See all 99 topics