Wrong result while declaring a variable inside Stored procedure

Question

Following is a simple stored procedure to calculate male count from a table , I have declared a variable total_count inside the proc where i'm storing my result.

 DELIMITER //

 CREATE PROCEDURE GetMaleCount()
      BEGIN
 DECLARE total_count INT DEFAULT 0 ;
 SELECT COUNT(STUDENT_ID) INTO total_count
 FROM [table1]
 where STUDENT_GENDER = 'M' ;

     END //
 DELIMITER ;

   call GetMaleCount();
   select @total_count as tc;

When i executed this procedure i'm getting NULL as the answer, but when i seperately executed just the inner sql query i got the right answer 1852. have i declared the variable in the wrong way ?

Answer 1

total_count that you've declared is visible only in procedure. That is why it is NULL outside of it. You need to use OUT parameter when defining procedure:

DELIMITER //

CREATE PROCEDURE GetMaleCount(OUT total_count INT)
    BEGIN
        SELECT COUNT(STUDENT_ID) INTO total_count
        FROM [table1]
        where STUDENT_GENDER = 'M' ;
    END //
DELIMITER ;

call GetMaleCount(@total_count);
select @total_count as tc;