Creating a function in mysql

Question

I would like to create a function or procedure that I can use in mysql but I'm not sure what the syntax would be. What I want to do is take the value from a column and see if it's a 1 or 2, and based on that, return the value from either column A or B. So for example, my select function would be something like:

select a, b, c, functionA(c) from table;

Below is the pseudo-code of my function

functionA(int x){
      if(x==1)
          //return value in column A
      else
          //return value in column B
}

you can do this without using a function and it will perform better. select case when c=1 then a else b end from table — gordy
– gordy, Commented Jul 9, 2011 at 2:46
@oipsl: it is a time to remove SQL and mysql from known array in your profile )) — zerkms
– zerkms, Commented Jul 9, 2011 at 2:55

ahmet alp balkan · Accepted Answer · 2011-07-09 02:50:04Z

1

select a, b, c, IF(c=1, a, b) from table;

or you can use select case but I think IF is more readable.

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

answered Jul 9, 2011 at 2:50

ahmet alp balkan

45.9k43 gold badges152 silver badges224 bronze badges

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

Comments

gordy · Accepted Answer · 2011-07-09 02:56:03Z

0

A function won't be able to return anything from the table that you don't pass in. To do what you want requires 3 parameters:

create function functionA(p1 integer, p2 integer, p3 integer) returns integer
begin
    if p1 = 1 then
        return p2
    else
        return p3
    end if;
end;

..and your query would be:

select a, b, c, functionA(c, a, b) from table;

However it's more efficient and easier to just case or if for such a simple query:

select case when c=1 then a else b end from table;

answered Jul 9, 2011 at 2:56

gordy

9,9713 gold badges37 silver badges49 bronze badges

Collectives™ on Stack Overflow

Creating a function in mysql

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