Using variables in stored procedures

Question

Trying to figure out why it is NULL. I was expecting 7 to be printed.

mysql> set @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> call getAuthorCount(@total);
+------------------------+
| count(distinct author) |
+------------------------+
|                      7 |
+------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> select @total as totalauthors;
+--------------+
| totalauthors |
+--------------+
|         NULL |
+--------------+

The procedure,

mysql> create procedure getAuthorCount(out authorcount int)
    -> begin
    ->  select count(distinct author) from libbooks;
    -> end
    -> //

Devart · Accepted Answer · 2012-09-14 12:18:04Z

2

You should use INOUT parameter -

CREATE PROCEDURE getAuthorCount(INOUT authorcount INT)
BEGIN
  SELECT count(DISTINCT author) FROM libbooks;
END

Examples:

When @total value is as is (0 in, 0 out):

DROP PROCEDURE getAuthorCount;
DELIMITER $$
CREATE PROCEDURE getAuthorCount(INOUT authorcount INT)
BEGIN
  -- SET authorcount = 100;
END$$
DELIMITER ;

SET @total = 0;
CALL getAuthorCount(@total);
SELECT @total AS totalauthors;
+--------------+
| totalauthors |
+--------------+
|            0 |
+--------------+

When @total value is replaces with a new value in stored procedure:

DROP PROCEDURE getAuthorCount;
DELIMITER $$
CREATE PROCEDURE getAuthorCount(OUT authorcount INT)
BEGIN
  SET authorcount = 100;
END$$
DELIMITER ;

SET @total = 0;
CALL getAuthorCount(@total);
SELECT @total AS totalauthors;
+--------------+
| totalauthors |
+--------------+
|          100 |
+--------------+

edited Sep 14, 2012 at 12:18

answered Sep 14, 2012 at 11:18

Devart

120k23 gold badges172 silver badges189 bronze badges

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

4 Comments

John Eipe Over a year ago

thanks. but i read that stored procedure can change this(OUT) parameter and pass back to the calling program.

John Eipe Over a year ago

why do I need to use IN or INOUT when i'm not planning to send in a value

Devart Over a year ago

Because parameter OUT authorcount = NULL by default, you should set new value, e.g. 'SET authorcount = 100' in the procedure's body.

Devart Over a year ago

Take a look at examples in the answer.

Collectives™ on Stack Overflow

Using variables in stored procedures

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related