Functions

[Darren Whaley]

Can someone explain how to get Functions to work in MySql? I have some User Defined Functions in SQL Server I need to transfer to MySql. The docs make a lot of reference to C/C++, but I don't know much about that.

Thanks

[kim_b]

Here is a function that I have working on Mysql

DELIMITER $$;

CREATE FUNCTION `pricechange_lastprice`(i_exchange int,i_symbol varchar(20)) RETURNS float
BEGIN
   DECLARE i_price ,t_price float;
select price into t_price from pricechange
    where exchange = i_exchange
        and
    symbol = i_symbol
order by sequence desc limit 1;
set i_price = coalesce(t_price,0);
RETURN i_price;
END$$

DELIMITER ;$$

Perhaps this will help you.  This function picks up the last price for a stock on a stock exchange by reading a table and returns either the value found or if it can't find one 0.

There is a good pdf file you can download from the MySql site that explains stored procedures and functions.
You can find it here:  http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf

When returning a value from a table, you have to 'select into' a variable.  Remember you can only return a single value from a function.

You execute the stored procedure using
"select pricechange_lastprice(19,'msft')" where 19  is the exchange code and 'msft' is the symbol.

I actually call this from a stored procedure but I  could just as easily call it from directMySql.

Good luck!