Null Value in MySQL during Procedure

Question

Procedure

delimiter $$ 
CREATE procedure sp_count_accounts(in inCX varchar(45), out countFD int, out countSA int) 
begin
    declare countFD, countSA int default 0;
    select count(*) into countFD from account where ACC_TYPE='FD' and CUS_NAME = inCX;
    select count(*) into countSA from account where ACC_TYPE='SA' and CUS_NAME = inCX;
end $$ 
delimiter ;

CALL sp_count_accounts('Alex', @SA, @FD); 
SELECT @SA as Savings, @FD as 'Fixed Deposit';

Test Result

Savings Fixed Deposit
1       2

However, i am getting null instead of the values.

I tried looking at my notes but im unsure whats going on

Answer 1

When youi declare Variable that have the same name as the output parameter, you will only fill the variable, but the out parameters stay the saem in your case NULL.

so remove the declare and you get your wanted result

CREATE tABLE account (ACC_TYPE vaRCHAR(2), CUS_NAME VARCHAR(20))

INSERT INTO account VALUES ('FD','Alex'), ('SA','Alex'), ('SA','Alex')

CREATE procedure sp_count_accounts(in inCX varchar(45), out countFD int, out countSA int) 
begin
    #declare countFD, countSA int default 0;  <-- removed
    select count(*) into countFD from account where ACC_TYPE='FD' and CUS_NAME = inCX;
    select count(*) into countSA from account where ACC_TYPE='SA' and CUS_NAME = inCX;
end

CALL sp_count_accounts('Alex', @SA, @FD); 

SELECT @SA as Savings, @FD as 'Fixed Deposit';

Savings	Fixed Deposit
1	2

fiddle