MYSQL Select Into Variable not Storing result in Variable in Stored Procedure.

Question

I have a stored procedure that selects a aggregated SUM into a local variable.

When I run the code (A Select into Statement) in MYSQL Workbench without running the Stored Procedure and then Select the Variable. I see the result I expect.

When the exact same select into statement is run within the Workbench, or external application, by calling the stored procedure. The local variable always return NULL.

Does any one know why it works outside the Stored Procedure but NOT when running the procedure.

I have included the stored Procedure code and some examples of WorkBench returning the results and not returning the results in the proc.

I even hard coded the Input Variables. It still doesn't work.

Cheers.

Tim.

DROP PROCEDURE MPSH.proc_entry_overallStatusUpdate;

DELIMITER //
CREATE PROCEDURE MPSH.proc_entry_overallStatusUpdate
(IN IN_entryID int(11), IN_entryStatusID int(11))
BEGIN   

    DECLARE approval int;

    SELECT  
        SUM(approval) 
    INTO 
        @approvalScore 
    FROM MPSH.tbl_EntryApprovals 
    WHERE entryID = 8
    AND entryStatusID= 8
    AND sysActive = 1
    GROUP BY entryID, entryStatusID;

   SELECT 'SUM',  @approvalScore ;


    IF @approvalScore   IS NOT NULL THEN 
        IF @approvalScore  >= 2 THEN
            #Approved
            SELECT 'Approve = 2';
            SET approval = 2;
        ELSEIF (@approvalScore  BETWEEN -1 AND 2) THEN
            #Awaiting Review
            SELECT 'Approve = 0';
            SET approval = 0;
       ELSE 
            #Entry Rejected.
            SELECT 'Approve = 3';
            SET approval = 3;
       END IF;


        #Update the Overall Status of an Entry. 
        UPDATE MPSH.tbl_EntryStatus
            SET  overallStatusID = approval
                    ,sysUpdated = NOW()
        WHERE entryStatusID = N_entryStatusID
        AND sysActive = 1;

    END IF;

END // 
DELIMITER ;

CALL MPSH.proc_entry_overallStatusUpdate(8,8);

Thanks for your help.

You have not been consistent with variable names DECLARE approval int / INTO @approvalScore or types (declared or @) see dev.mysql.com/doc/refman/5.7/en/select-into.html for select into syntax BTW either a declared variable or an @ variable will work - but note a declared variable is not an @ variable. — P.Salmon
– P.Salmon, Commented Mar 1, 2018 at 13:12
Sorry @P.Salmon just wondering if you can point out the line I am inconsistent on. I am using (@)approvalScore and I am using "approval" but they are different variables. do I need to use the same variable type throughout the whole SP. I am about to read the Doco also. Thanks But any other reasoning could help. — Tim Hiatt
– Tim Hiatt, Commented Mar 1, 2018 at 13:22

Mittal Patel · Accepted Answer · 2018-03-01 08:11:22Z

4

To store the data into the variable in MySQL, you can do it as below:

Solution1

SET @approvalScore = 0;

SELECT  
    @approvalScore :=SUM(approval)  
FROM MPSH.tbl_EntryApprovals 
WHERE entryID = 8
AND entryStatusID= 8
AND sysActive = 1
GROUP BY entryID, entryStatusID;

Solution2

SELECT  
    SUM(approval) 
INTO 
   approvalScore 
FROM MPSH.tbl_EntryApprovals 
WHERE entryID = 8
AND entryStatusID= 8
AND sysActive = 1
GROUP BY entryID, entryStatusID;

As a good practice, always declare the variable and set it's default value.

edited Mar 1, 2018 at 8:11

answered Mar 1, 2018 at 7:12

Mittal Patel

2,74716 silver badges23 bronze badges

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

4 Comments

Tim Hiatt Over a year ago

Thnaks however this doesn't work inside a Stored Procedure. I get a Syntax error when trying to use declare @approvalScore int;

Mittal Patel Over a year ago

Remove the declare and then try... Check both solution

Tim Hiatt Over a year ago

I Tried both solutions above. Option 1 works when I run it natively in Workbench. Just as a command. When I save it in the Stored Proc and Run the Proc. It still returns NULL. Something must be wrong with the way it is handling the variable in the proc.

Mittal Patel Over a year ago

In one of my SP, I have handle variable as first solution only and it works fing

Kedar Limaye · Accepted Answer · 2018-03-01 07:17:22Z

2

use- declare @approvalScore int; and initialize it

answered Mar 1, 2018 at 7:17

Kedar Limaye

1,0418 silver badges15 bronze badges

2 Comments

Tim Hiatt Over a year ago

This doesn't work inside a Stored Procedure. I get a Syntax error when . trying to use declare @approvalScore int;

Kedar Limaye Over a year ago

what error did you get? did you use it after your 'DECLARE approval int;' statement?

Tim Hiatt · Accepted Answer · 2018-03-01 14:50:37Z

1

@P.Salmon had the correct point here. After recutting some of my code and re-reading the documentation. I realised I should not mix @vairables with declare variables. I switch the SP to run with just @variables and set each of them when they first appear in the SP and the procedure now works fine.

Thanks.

answered Mar 1, 2018 at 14:50

Tim Hiatt

1132 silver badges14 bronze badges

Collectives™ on Stack Overflow

MYSQL Select Into Variable not Storing result in Variable in Stored Procedure.

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related