0

I have a stored procedure which calls a user function similar to the structure below:

CREATE PROCEDURE `MyProc`()
BEGIN
    SELECT MyFunc(Column1), @running := @running + Column2
    FROM MyTable JOIN (@running := 0);
END

CREATE FUNCTION `MyFunc`(ID INT)
RETURNS INT
BEGIN
    SELECT Column1, @running := @running + Column2
    FROM OtherTable JOIN (@running := 0) WHERE MyTableID = ID;

    RETURN -- some value calculated later
END

The issue here is that the user variable @running is reset when calling the function which is not what I intend. Ideally they have a completely different scope and would like to use a local variable but I don't know if its possible to increment a local variable within a query like I am doing above.

Is something like this possible?

CREATE PROCEDURE `MyProc`()
BEGIN
    DECLARE running DECIMAL;
    SET running = 0;

    SELECT MyFunc(Column1), running = running + Column2
    FROM MyTable;
END

CREATE FUNCTION `MyFunc`(ID INT)
RETURNS INT
BEGIN
    DECLARE running DECIMAL;
    SET running = 0;

    SELECT Column1, running = running + Column2
    FROM OtherTable WHERE MyTableID = ID;

    RETURN -- some value calculated later
END

Or will I just have to manually keep track of all user variables in my database and make sure they are named uniquely? e.g. @running1 and @running2

Hopefully there is a better solution.

fungus1487
  • 1,760
  • 2
  • 20
  • 39
  • Have you tried explicitly using `declare` for the variables? – Gordon Linoff Apr 16 '14 at 13:21
  • As in my second example? If that's what you mean then yes. MySQL just gives me a value of 0 for every row though, it appears as if it doesnt set the running variable for each row. – fungus1487 Apr 16 '14 at 13:34

0 Answers0