MYSQL SELECT statement returns null when called inside a Stored Procedure

Question

Hi I am trying to create a simple procedure with a select statement inside it :

DELIMITER //
CREATE PROCEDURE testProcedure(OUT login_id VARCHAR(30))
BEGIN
    SELECT LOGIN_ID INTO login_id FROM USER_REG_MST WHERE USER_MOBILE_NO = 123;
END//
DELIMITER ;

The select satatement when called out side the proedure independently returns a value, but when the select is called inside a stored procedure it returns a null.

SELECT LOGIN_ID INTO login_id FROM mystrodb.USER_REG_MST WHERE USER_MOBILE_NO = 123;

Gives me a value. But,

CALL testProcedure(@out);

SELECT @out;

returns a NULL.

Note: the columns is of type VARCHAR(30) (additional info)

I need to make one of two changes to make it work: either change the name of the procedure variable to be something other than login_id, or explicitly put the table name before the field name in the select. — Koen
– Koen, Commented Aug 19, 2018 at 11:49
Thank you koen, this works. Looks like LOGIN_ID is conflicting. — Nikhil Varma
– Nikhil Varma, Commented Aug 19, 2018 at 11:55

wchiquito · Accepted Answer · 2018-08-19 16:10:07Z

3

The problem is that for MySQL LOGIN_ID and login_id are the same variable, so it's not recommended to name parameters and variables as the name of their objects or entities (tables, columns, etc.) of your database, see C.1 Restrictions on Stored Programs :: Name Conflicts within Stored Routines.

A couple of options: 1) Change the name of the output parameter of the stored procedure. 2) Include a qualifier to LOGIN_ID column, see 9.2.1 Identifier Qualifiers.

...
SELECT `USER_REG_MST`.`LOGIN_ID` INTO `login_id` ...
...

See dbfiddle.

answered Aug 19, 2018 at 16:10

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

Comments

Lukasz Szozda · Accepted Answer · 2018-08-19 11:50:47Z

1

You need to set OUT parameter and make sure to distinguish between column name vs parameter name:

DELIMITER //
CREATE PROCEDURE testProcedure(OUT login_id VARCHAR(30))
BEGIN
    SET login_id = (SELECT u.LOGIN_ID 
                    FROM USER_REG_MST u WHERE USER_MOBILE_NO = 123);
END//
DELIMITER ;

DBFiddle Demo

edited Aug 19, 2018 at 11:50

answered Aug 19, 2018 at 11:41

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

MYSQL SELECT statement returns null when called inside a Stored Procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related