CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

MySql Case inside a function

I'm trying to achieve something like below, but I'm going all wrong - with the logic, the syntax an all else. Can you please help me?

I'm trying to SET STATIC = based on certain conditions. If this were PHP, I'd have used Break. I came this far, but nothing works. Can you see how to do this? 

CREATE DEFINER=`root`@`localhost` FUNCTION `caseTest`(`n` INT)
    RETURNS varchar(512)
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

  DECLARE x VARCHAR(1500); 
  DECLARE y VARCHAR(1500); 
  DECLARE static VARCHAR(1500); 

  CASE n
        WHEN '14' THEN SET x = 'Place Order Link';
        WHEN '01' THEN SET x = 'Cancel Order Link';
        WHEN '11' THEN SET x = 'Order Cancelled - Place order link';
        SET static = concat(<a href="">,x,</a>);

        WHEN '00' THEN SET x = 'Order - Under Process'; #No link here
        WHEN '10' THEN SET x = 'Cancel - Under Process'; #No link here
        SET static = x;

        ELSE SET static = 'Error generating link';
    END;

 set y = 'Flag Link ~ Edit Link ~ Move Link';

  RETURN concat(x,y);
END

Upvotes: 3

Views: 9976

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

You want to move the set outside the case statement:

  set x = case n 
        WHEN '14' THEN 'Place Order Link';
        WHEN '01' THEN 'Cancel Order Link';
        WHEN '11' THEN 'Order Cancelled - Place order link';
        WHEN '00' THEN 'Order - Under Process'; #No link here
        WHEN '10' THEN 'Cancel - Under Process'; #No link here
      end;
  set static = (case when n in ('14', '01', '11') then concat(<a href="">,x,</a>)
                     when n in ('00', '10') then x
                     ELSE 'Error generating link'
                end);

You can also do this with if. The set with case seems closer to your original logic.

I see no reason why the above should not work (except for possible syntax errors). You can try the if version:

 if n = '14' THEN x = 'Place Order Link';
 elseif n = '01' THEN x = 'Cancel Order Link';
 elseif n = '11' THEN x = 'Order Cancelled - Place order link';
 elseif n = '00' THEN x = 'Order - Under Process'; #No link here
 elseif n = '10' THEN x = 'Cancel - Under Process'; #No link here

 if n in ('14', '01', '11') then static = concat(<a href="">,x,</a>);
 elseif n in ('00', '10') then static = x;
 ELSE static = 'Error generating link';

Upvotes: 3

Related Questions