Declaring and using variables with select in mysql

Question

I have the following stored procedure. It does not seem to work. Is there anything that I'm doing wrong?

The error occurs when I declare role on the 4th line.

create procedure getRole(in x mediumint unsigned)
begin

DECLARE role INT DEFAULT 3
SELECT user_level INTO role  FROM login_users WHERE user_id = x

        CASE role  
            WHEN 1 THEN  

            WHEN 2 THEN  
                select
                 u.user_level As role,
                 a.id As id,
                 'advisor' As user
                from
                 login_users u
                 inner join login_levels l on u.user_level =l.id
                 inner join rp_adviser a on u.user_id = a.user_id
                where
                 u.user_id = 11;   
            ELSE  
                select
                 u.user_level As role,
                 s.id As id,
                 'student' As user
                from
                 login_users u
                 inner join login_levels l on u.user_level =l.id
                 inner join rp_student s on u.user_id = s.user_id
                where
                 u.user_id = 9;  
                END CASE;  
    end

Dewasish Mitruka · Accepted Answer · 2011-12-15 13:19:29Z

1

Try This :

DROP PROCEDURE IF EXISTS `getRole` $$
CREATE PROCEDURE `q1a`.`getRole` (in x mediumint unsigned)
BEGIN

DECLARE role INT DEFAULT 3;
SELECT user_level INTO role  FROM login_users WHERE user_id = x;

if(role = 1 or role = 1)
then
select
                 u.user_level As role,
                 a.id As id,
                 'advisor' As user
                from
                 login_users u
                 inner join login_levels l on u.user_level =l.id
                 inner join rp_adviser a on u.user_id = a.user_id
                where
                 u.user_id = 11;
else
select
                 u.user_level As role,
                 s.id As id,
                 'student' As user
                from
                 login_users u
                 inner join login_levels l on u.user_level =l.id
                 inner join rp_student s on u.user_id = s.user_id
                where
                 u.user_id = 9;
end if;

END $$

answered Dec 15, 2011 at 13:19

Dewasish Mitruka

2,8861 gold badge19 silver badges21 bronze badges

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

1 Comment

SupaOden Over a year ago

It wasnt working in the execute SQL statement, but it worked when I created a new routine.

Mahmoud Gamal · Accepted Answer · 2011-12-15 13:33:32Z

0

Actually the declare and use of the variavle role in the CASE statement in your query is ok but you need to separate the statements in the stored procedure with semicolone ; like t his:

DECLARE role INT DEFAULT 3;
SELECT user_level INTO role  FROM login_users WHERE user_id = x;

    CASE role
    ...

answered Dec 15, 2011 at 13:33

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

1 Comment

SupaOden Over a year ago

It wasnt working in the execute SQL statement, but it worked when I created a new routine. – asdgfas sagas just now

Collectives™ on Stack Overflow

Declaring and using variables with select in mysql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related