[How-to] MySQL function example.

Scenario:

Guys, this is a simple tutorial on how to create a MySQL function.
For example, I need a MySQL function that will take in 2 integer input parameters and return a tinyint value to user.


Solution:

Here is the sample function code:

View Code MYSQL
DELIMITER $$
 
DROP FUNCTION IF EXISTS `sampleFunc1`$$
 
CREATE FUNCTION `sampleFunc1`(
			a INT, 
			b INT
		) RETURNS TINYINT(1)
    DETERMINISTIC
BEGIN
	-- Function logic here
	DECLARE run TINYINT DEFAULT 0;
	IF (a+b) > 100 THEN
		SET run = 1;
	ELSE
		SET run = 2;
	END IF;
	RETURN run;
    END$$
 
DELIMITER ;

Sample result:

5 Comments to “[How-to] MySQL function example.”

  1. Bianco 12 April 2012 at 1:21 am #

    How do we pass an optional parameter with a default value as an argument to a stored function?

  2. h2Guru 12 April 2012 at 5:15 pm #

    Hi Bianco,

    So far base on my understanding, it is not possible to do so for MySQL.
    You might need to send in the extra parameters for all your call function.

  3. Santanu 29 July 2012 at 2:55 pm #

    Thanks for this example.

  4. LeGenDary 24 April 2013 at 9:35 pm #

    @Biaco: Make a simple trick with if,else!

  5. EndErr 28 June 2013 at 1:43 am #

    How to return a bunch of record from a function call?


Leave a Reply