How to print a variable in SQL stored procedure?

user_2074779 0
Viewed 1429
CREATE DATABASE loan_data;
USE loan_data;
    
CREATE TABLE loan_user (
    id long,
    amount long
    );
    
INSERT INTO loan_user(id, amount)
values (1,7000), (2,7000), (3,5000);

DELIMITER $$
CREATE PROCEDURE loan_check()
BEGIN
DECLARE a int;  
select a = count(amount) from loan_user where amount=7000;
print a;
END $$

DELIMITER ;
CALL loan_check();

DROP DATABASE loan_data;

I want to print the a variable in the above code. But the code returns

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a;
END' at line 5

How to fix this?

ubuntu mysql sql
2 Answers

You can use an OUT variable

CREATE TABLE loan_user (
    id long,
    amount long
    );

INSERT INTO loan_user(id, amount)
values (1,7000), (2,7000), (3,5000);

CREATE PROCEDURE loan_check(OUT _answer BIGINT)
BEGIN
DECLARE a int;  
select  count(amount) into a from loan_user where amount=7000;
SET _answer =  a;
END

SET @answer := 0;
CALL loan_check(@answer);
SELECT IF(@answer = 2, 'yes',@answer)as answer

✓

✓

| answer |
| :----- |
| yes    |

db<>fiddle here*

Or simply don_'t use INTO or :=

CREATE TABLE loan_user (
    id long,
    amount long
    );

INSERT INTO loan_user(id, amount)
values (1,7000), (2,7000), (3,5000);

CREATE PROCEDURE loan_check()
BEGIN
DECLARE a int;  
select  count(amount)  from loan_user where amount=7000;

END

CALL loan_check();

| count(amount) |
| ------------: |
|             2 |

✓

CREATE PROCEDURE loan_check2(_inc int)
BEGIN
DECLARE a int;  
select  IF(count(amount) = 2 , 'yes',COUNT(amount)) as answer from loan_user where amount=_inc;

END

CALL loan_check2(7000);

| answer |
| :----- |
| yes    |

✓

db<>fiddle here

user_51935360

If it's just yo debug your stored procedure, PRINT is not a MySQL stored procedure command, you should use something similar to this:

 SQL  CREATE PROCEDURE demo_a()
    - BEGIN
    - DECLARE a int;
    - SELECT SUM(1+2) into @a;
    - SELECT @a;
    - END ||

 SQL  delimiter ;

 SQL  call demo_a();
+----+
| @a |
+----+
|  3 |
+----+
1 row in set (0.0014 sec)

Now it you want to get that value as an output of your stored procedure, you should use an OUT variable as you have been advised too.

user_77623880
Related