CODEWITHSUNDEEP
mysql
Dan35
Dan35

Reputation: 1

Cause to return null values when calling a function

I am trying to create a function that extracts from my table, any employee name that begins with J

delimiter $$
create function myfunction(nume_persoane varchar (30)) returns int deterministic
begin
declare omcucap int;
select first_name into omcucap  from employee where id = nume_persoane and first_name = 'J%';
return omcucap;
end $$

When I call the function select myfunction(first_name) from employee; it returns null. Why is that? What is the explication..

Upvotes: 0

Views: 69

Answers (3)

Zelldon
Zelldon

Reputation: 5516

Your parameter 'nume_persoane' is set to the id of the employee

select first_name into omcucap from employee where id = nume_persoane and first_name = 'J%';

but you calls your function with the first_name

select myfunction(first_name)

and also the first_name is not are int or? But you try to insert the first_name to the declared variable

declare omcucap int;
select first_name into omcucap ...

UPDATE

Use these function :

delimiter $$
create function myfunction(p1 int) returns int
begin
declare eID int;
select id into eID  from employee where id = p1 and first_name LIKE 'J%';
return eID;
end $$

and executed the function with these select statement:

SELECT myfunction(id) FROM employee;

Upvotes: 0

JoDev
JoDev

Reputation: 6873

To complete shazin answer, to make it work, you propably need to declare omcucap as varchar.

declare omcucap varchar(first_name size);

And I don't think id is varchar. So nume_persoane would be int(id size) instead. And the return type would be varchar(first_name size)

You function will be 

delimiter $$
create function myfunction(nume_persoane int(10)) returns varchar(50) deterministic
begin
declare omcucap varchar(50);
select first_name into omcucap  from employee where id = nume_persoane and first_name LIKE 'J%' LIMIT 1;
return omcucap;
end $$

Where 50 is you first_name size and 10 is your id size.
I add LIMIT 1 statement to avoid few results issue.

EDIT
If you wan't a default value, used : 

select IFNULL(first_name, 'default_value') into omcucap[...]

Upvotes: 0

shazin
shazin

Reputation: 21883

omcucap int;

Is your first_name is an int type? I don't think so.

And consider the following change

UPPER(first_name) LIKE 'J%';

you can't use = for '%'

Upvotes: 1

Related Questions