MySQL Assign Return Value to Variable

Question

How do you declare variable and set a value which is a return from a query on them later.

Sample Stored Procedure:

DELIMITER $$

CREATE PROCEDURE `sampledb`.`SetVariableEx`()

    BEGIN

        -- declare variable
        DECLARE xVarA INT;                
        DECLARE xVarB INT;      

        -- in this line, i would like to set a value on xVarA which is a COUNT
        -- of record from table SINGLETABLE
        -- i am getting error on this line.
        SELECT xVarA := COUNT(*) FROM SingleTable;

        -- the value of xVarA is added by 1 and set it to xVarB
        SET xVarB = xVarA + 1;

        -- insert the value of xVarB to the table SINGLETABLE
        INSERT INTO SingleTable(SingleColumn) VALUES (xVarB);

        -- lastly, display all records.
        SELECT * FROM SingleTable;

    END$$

DELIMITER ;

how would i do that?

Rich O'Kelly · Accepted Answer · 2013-09-20 17:18:35Z

3

Try the following:

SET xVarA := (SELECT COUNT(*) FROM SingleTable);

However, for this example, have you considered using an auto auto-incrementing value, rather than managing the value yourself?

edited Sep 20, 2013 at 17:18

answered Jan 9, 2012 at 11:34

Rich O'Kelly

41.8k9 gold badges87 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Woo Over a year ago

AutoIncrement is not my point in the qestion but rather setting the value from the query to a variable. SET xVarA := SELECT COUNT() FROM SingleTable; does not work but by putting the query inside a Parenthesis works. SET xVarA := (SELECT COUNT() FROM SingleTable); thanks!

Rich O'Kelly Over a year ago

@491243 Somehow missed this previously, rather belatedly got round to updating my answer. Thanks.

Collectives™ on Stack Overflow

MySQL Assign Return Value to Variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related