CODEWITHSUNDEEP
mysqlstored-procedures
SupaOden
SupaOden

Reputation: 742

Declaring and using variables with select in mysql

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

Upvotes: 0

Views: 324

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

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
    ...

Upvotes: 0

Dewasish Mitruka
Dewasish Mitruka

Reputation: 2896

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 $$

Upvotes: 1

Related Questions