SA12 stored procedure/function bug?

[Former Member]

I was investigating a mysterious problem which I now suspect may be a bug, as illustrated by the two simple stored functions below:-

create function getlv1() returns long varchar begin declare @LV long varchar; set @LV = @LV || 'a'; return @LV; end;

create function getlv2() returns long varchar begin declare @LV long varchar; declare @i integer; set @LV = @LV || 'a'; return @LV; end;

When I 'select getlv1()' in dbisql I receive an error message "Column '@lv' not found", but 'select getlv2()' works just fine. The only difference between the two definitions is the unused/unwanted declaration of the variable @i.

[Former Member]

Thank you for reporting this problem.

You could work around the problem by using

declare @lv long varchar = NULL;

instead of the current declaration in the first function.

Why are you using @lv in the calculation? Using the variable provides no benefit for your calculation. Is there a different problem you are trying to solve?

[Former Member]

Thank you for your suggestion.

The samples I provided were solely to illustrate what I thought might be a problem - one which arose when I split a very old stored procedure into umpteen separate bits. The original SP declared more than one variable (as in the second sample), and the return value was originally the result of concatenating many strings together, and this is where the unnecessary initial concatenation came from when I removed some unwanted prior code.

[Former Member]

Thank you for reporting this problem. I do believe that you have found a bug. We will investigate and provide a fix as needed.