CODEWITHSUNDEEP
mysqlsql-server
Kinyanjui Kamau
Kinyanjui Kamau

Reputation: 1936

Converting MSSQL function to MySQL

I am having trouble converting the following MSSQL function to MySQL.

MSSQL

USE [EkoPayroll]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_GetLowestOf3]    Script Date: 01/31/2014 13:52:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   FUNCTION [dbo].[fn_GetLowestOf3] (
    @num1 numeric,
    @num2 numeric,
    @num3 numeric
 )  
RETURNS numeric AS  
BEGIN
    declare @num numeric
    set @num=@num1
    IF @num2<@num1
    SET @num=@num2 

    IF @num3<@num
    SET @num=@num3

    RETURN @num
END

MySQL

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `eko_payroll`.`GetLowestOf3`(num1 numeric,
    num2 numeric,
    num3 numeric)
    RETURNS 
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
numeric
    BEGIN
declare num numeric
    set num=num1
    IF num2<num1
    SET num=num2 

    IF num3<num
    SET num=num3

    RETURN num
    END$$

DELIMITER ;

I get the error 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set num=num1
    IF num2<num1
    SET num=num2 
    IF num3<num
    SET num=num3

How do I fix this?

Upvotes: 0

Views: 110

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166396

I think you are missing the THEN and END IF keywords

Have a closer look at the syntax specified IF Syntax

So something like

CREATE

    FUNCTION `GetLowestOf3`(num1 numeric,
    num2 numeric,
    num3 numeric)
    RETURNS 
numeric
BEGIN
    declare num numeric;
    set num=num1;
    IF num2<num1 THEN
      SET num=num2 ;
    END IF;

    IF num3<num THEN
      SET num=num3;
    END IF;

    RETURN num;
END

SQL Fiddle DEMO

You could also try

CREATE

    FUNCTION `GetLowestOf3`(num1 numeric,
    num2 numeric,
    num3 numeric)
    RETURNS 
numeric
BEGIN
    declare num numeric;
    SET num = 
    (
      SELECT MIN(Val)
      FROM 
            (
              SELECT num1 Val
              UNION ALL
              SELECT num2
              UNION ALL
              SELECT num3
             ) t
     );

    RETURN num;
END

SQL Fiddle DEMO

MySQL Does have a LEAST function

With two or more arguments, returns the smallest (minimum-valued) argument.

SELECT LEAST(3,2,1)

Upvotes: 2

Related Questions