I Can't get the from 'USERA' condition. even though I have values in table. But I can get the values for 'PERSN' and 'ORG'

Franklin R 0
Viewed 21
CREATE DEFINER=`root`@`localhost` 
FUNCTION `test`( `@REFERENCE_ID` bigint, `@CustomerType` varchar(25)) 
RETURNS varchar(200) CHARSET utf8mb4
DETERMINISTIC
BEGIN
DECLARE ContactNumber VARCHAR(200);
SET ContactNumber = (
    select case 
        when (`@CustomerType` = 'PERSN')
        then (select CONTACT_NO from app_person where PERSON_ID = `@REFERENCE_ID`)
        when (`@CustomerType` = 'USERA')
        then (select CONTACT_NUMBER from itg_user where USER_SERIAL_ID = `@REFERENCE_ID`)
        when (`@CustomerType` = 'ORG')
        then (select CONTACT_NO from app_organization where ORG_ID = `@REFERENCE_ID`)
        else 'N/A' 
        END
    FROM app_person PR  
    where PR.PERSON_ID = `@REFERENCE_ID`
    );
RETURN ifnull(ContactNumber,'NA');
END
stored-procedures function mysql
1 Answers

The dangling FROM app_person PR where PR.PERSON_ID = @REFERENCE_ID is going to override the case selects for example

drop function if exists f;
drop table if exists app_person,itg_user,app_organisation;
create table app_person(contact_no int,person_id int);
create table itg_user(contact_no int,user_serial_id int);
create table app_organisation(contact_no int,org_id int);

insert into app_person values(1,1);
insert into itg_user values(2,2);
insert into app_organisation values(3,1);
delimiter $$
CREATE FUNCTION f( `@REFERENCE_ID` bigint, `@CustomerType` varchar(25)) 
RETURNS varchar(200) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
DECLARE ContactNumber VARCHAR(200);
    SET ContactNumber = 
    (select 
    case when (`@CustomerType` = 'PERSN') then (select CONTACT_NO from app_person where PERSON_ID = `@REFERENCE_ID`)
          when (`@CustomerType` = 'USERA') then (select CONTACT_No from itg_user where USER_SERIAL_ID = `@REFERENCE_ID`)
          when (`@CustomerType` = 'ORG')   then (select CONTACT_NO from app_organisation where ORG_ID = `@REFERENCE_ID`)
    else 'N/A' END
    FROM app_person PR  where PR.PERSON_ID = `@REFERENCE_ID`
    );

    RETURN ifnull(ContactNumber,'NA');
END $$

delimiter ;

select f(2,'USERA');

+--------------+
| f(2,'USERA') |
+--------------+
| NA           |
+--------------+
1 row in set (0.002 sec)

Comment it out 

CREATE FUNCTION f( `@REFERENCE_ID` bigint, `@CustomerType` varchar(25)) 
RETURNS varchar(200) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
DECLARE ContactNumber VARCHAR(200);
    SET ContactNumber = 
    (select 
    case when (`@CustomerType` = 'PERSN') then (select CONTACT_NO from app_person where PERSON_ID = `@REFERENCE_ID`)
          when (`@CustomerType` = 'USERA') then (select CONTACT_No from itg_user where USER_SERIAL_ID = `@REFERENCE_ID`)
          when (`@CustomerType` = 'ORG')   then (select CONTACT_NO from app_organisation where ORG_ID = `@REFERENCE_ID`)
    else 'N/A' END
    #FROM app_person PR  where PR.PERSON_ID = `@REFERENCE_ID`
    );

    RETURN ifnull(ContactNumber,'NA');
END $$

select f(2,'USERA');

+--------------+
| f(2,'USERA') |
+--------------+
| 2            |
+--------------+
1 row in set (0.002 sec)

Note I have used CONTACT_NO in all 3 tables , if you really use contact_number then edit appropriately.

BTW I don't like this code style specifically I dislike (and didn't think you could) declaring datatypes for user defined variables, I don't like their use in the procedure and would prefer local variables, I also don't like select case and would for clarity (in my opionion) use if..elseif..end if construct.

P.Salmon0
Related