OUT parameter declaration in stored procedure

Question

Excuse me for the pretty simple question, but I can't understand what I have to do from similar questions.

I have this code, It works but I got asked to insert input value for CountMacro1, CountMacro2, CountMacro3, CountMacro4, which is always 0. How can I explain to mysql that I want those variables set to 0? set @CountMacro1 = 0; seems not to work, why? Thanks everyone :D

Mysql workbench do a call and a select like this:

set @CountMacro1 = 0;
set @CountMacro2 = 0;
set @CountMacro3 = 0;
set @CountMacro4 = 0;
call dbcbt.CountMacro_Audit_Scaduti(@CountMacro1, @CountMacro2, @CountMacro3, @CountMacro4);
select @CountMacro1, @CountMacro2, @CountMacro3, @CountMacro4;

Thats the call in php:

$conn = new PDO("mysql:host=$servername;dbname=dbcbt", $username, $password);


$sql = 'CALL CountMacro_Audit_Scaduti(@CountMacro1_OUT,@CountMacro2_OUT,@CountMacro3_OUT,@CountMacro4_OUT)';
$stmt = $conn->prepare($sql);

$CountMacro1_OUT = 0;
$CountMacro2_OUT = 0;
$CountMacro3_OUT = 0;
$CountMacro4_OUT = 0;

$stmt->bindParam(1, $CountMacro1_OUT, PDO::PARAM_INT, 10);
$stmt->bindParam(2, $CountMacro2_OUT, PDO::PARAM_INT, 10);
$stmt->bindParam(3, $CountMacro3_OUT, PDO::PARAM_INT, 10);
$stmt->bindParam(4, $CountMacro4_OUT, PDO::PARAM_INT, 10);

print "Values of bound parameters _before_ CALL:\n";
print "  1: {$CountMacro1_OUT} 2: {$CountMacro2_OUT} 3: {$CountMacro3_OUT} 4: {$CountMacro4_OUT}\n";

$stmt->execute();

print "Values of bound parameters _after_ CALL:\n";
print "  1: {$CountMacro1_OUT} 2: {$CountMacro2_OUT} 3: {$CountMacro3_OUT} 4: {$CountMacro4_OUT}\n";

CREATE DEFINER=`root`@`localhost` PROCEDURE `CountMacro_Audit_Scaduti`(
OUT CountMacro1 int,
OUT CountMacro2 int,
OUT CountMacro3 int,
OUT CountMacro4 int
)
BEGIN
set @CountMacro1 = 0;
set @CountMacro2 = 0;
set @CountMacro3 = 0;
set @CountMacro4 = 0;

select count(*)
INTO CountMacro1
from t_audit
WHERE (t_audit.Data_Scadenza < NOW()) AND (t_audit.Data_Completamento ="" OR t_audit.Data_Completamento is null) AND (t_audit.Macro1= 1 OR t_audit.Macro2= 1 OR t_audit.Macro3= 1);

select count(*)
INTO CountMacro2
from t_audit
WHERE t_audit.Data_Scadenza < NOW() AND (t_audit.Data_Completamento ="" OR t_audit.Data_Completamento is null) AND (t_audit.Macro1= 2 OR t_audit.Macro2= 2 OR t_audit.Macro3= 2);

select count(*)
INTO CountMacro3
from t_audit
WHERE t_audit.Data_Scadenza < NOW() AND (t_audit.Data_Completamento ="" OR t_audit.Data_Completamento is null) AND (t_audit.Macro1= 3 OR t_audit.Macro2= 3 OR t_audit.Macro3= 3);

select count(*)
INTO CountMacro4
from t_audit
WHERE t_audit.Data_Scadenza < NOW() AND (t_audit.Data_Completamento ="" OR t_audit.Data_Completamento is null) AND (t_audit.Macro1= 4 OR t_audit.Macro2= 4 OR t_audit.Macro3= 4);

END

User defined(at varaibles) and declared variables are not the same thing please read https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql — P.Salmon, Jan 31 '20 at 11:52

Arun Palanisamy · Answer 1 · 2020-01-31T10:43:17.507

0

You can declare Local variables and then you can assign the default value.

CREATE PROCEDURE MYPROC(var OUT) 
BEGIN   
   DECLARE CountMacro1 INT DEFAULT 0;  
   DECLARE CountMacro2 INT DEFAULT 0;
   DECLARE CountMacro3 INT DEFAULT 0;  
   DECLARE CountMacro4 INT DEFAULT 0;
   --Your code
END;

In your case, you are trying to assign default value to @CountMacro1 which is User defined variables and in your query you are assigning the count to local variable CountMacro1. Both are different.

You can run the below procedure to see the difference

CREATE PROCEDURE prc_test ()
BEGIN
    DECLARE CountMacro1  INT DEFAULT 1;
    SET @CountMacro1  = 2;
    SELECT  CountMacro1 , @CountMacro1 ;
END;

OUTPUT

+-------------+--------------+
| CountMacro1 | @CountMacro1 |
+-------------+--------------+
| 1           | 2            |
+-------------+--------------+

edited Jan 31 '20 at 10:43

answered Jan 31 '20 at 10:29

Arun Palanisamy

5,281
6
28
53

THX for the answer, I understand that CountMacro1 is different from @CountMacro1. Where CountMacro1 is the local variable. But why when I insert 0,0,0,0 manually as imput values ( from the mysql workbench) I got the correct count output? In addition I tried "DECLARE CountMacro1 INT DEFAULT 0; " but he still ask me to imput values. – Domenico Schitti Jan 31 '20 at 12:27
Sorry,I didn't get your point. I have created a similar procedure here -> [CLICK THIS DEMO](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cdeacd70bac986d28e9a6e5cb84774c2) and it is working fine. I'm able to get the `default` value for `CountMacro2`. Can you please replicate your issue here? @DomenicoSchitti – Arun Palanisamy Jan 31 '20 at 14:01
The problem is that when I run the procedure I still get this input box where he ask me to insert the values: https://ibb.co/Rz5t82t. Sorry but I cant understand how to change it D:, I just want my procedure to give in output my four row count in four variables. – Domenico Schitti Jan 31 '20 at 16:40
How are you calling the procedure? Are you running both `call()` and `select` together? [Check this link once](https://www.aspforums.net/Threads/591329/Solved-Execute-Stored-Procedure-with-Output-Parameter-in-MySql-database/) – Arun Palanisamy Jan 31 '20 at 16:52
I'm testing it on the workbench clicking on the lighting button next to the stored procedure name, the program do a call and a select, like Im writing up, in the question. – Domenico Schitti Jan 31 '20 at 17:06
This looks strange. What happens when you run manually `call` and `select`? instead of lighting button? @DomenicoSchitti – Arun Palanisamy Feb 03 '20 at 06:37
He asks for input numbers and I have to insert "0" in the four text boxes. I tried to call the stored procedure in php but I get always "0" as output. In the bench after that I fill the text boxes I get the correct values in output. I'm showing the php script in the question. – Domenico Schitti Feb 07 '20 at 17:06

OUT parameter declaration in stored procedure

1 Answers1