SQL user defined function not run

Question

I have situation where I want to generate invoice_id sequential for different product for multiple cities. I want to get generated invoice id according to different product and city.

My table temp look like

id  |order_id |product|city|invoice_id 
1   | 123     |  1    | 1  | FPU1
2   | 124     |  6    | 1  | PPU1

I want to get next invoice_id for product 1 and city 1 is FPU2.

For product 1 and city 2 is FBN1,product 6 and city 1 is PPU2 and so on ....

I create function but not run.Is anything wrong in function?

  CREATE function generate(p_id INT,  c_id INT) 
    returns VARCHAR(50) 

    BEGIN 
      -- DECLARE v_new_id VARCHAR(50); 
      SELECT Concat(( CASE 
                        WHEN t.product = 1 THEN "f" 
                        WHEN t.product = 6 THEN "p"    end ),
 c.city_name, Cast(RIGHT(t.invoice, Length(t.invoice) - 3)  AS  UNSIGNED) + 1
                   ) v_new_id 
      FROM   temp AS t 
             JOIN city c 
               ON c.city_id = t.city 
      WHERE  t.product = p_id 
             AND t.city = c_id; 

      RETURN( v_new_id ); 
    end;

Get syntax error:

#1064 - 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 '' at line 15

###Line 15 is AND t.city = c_id;

i advice you to make a view instead where you generate the `invoice_id ` — Raymond Nijland, Nov 19 '18 at 13:31
Perhaps you didn't set delimiters - https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html — P.Salmon, Nov 19 '18 at 13:42
also you cannot return a result set from a trigger, perhaps ) v_new_id should be ) into v_new_id and the declared variable uncommented. — P.Salmon, Nov 19 '18 at 13:52

score 0 · Accepted Answer · answered Nov 19 '18 at 14:40

0

Need to set delimiters .

Thanks @P.Salmon for valuable comment and link

For set delimiter manually follow

For phpmyadmin user follow this link

answered Nov 19 '18 at 14:40

Satish

696
1
11
22

SQL user defined function not run

1 Answers1