CODEWITHSUNDEEP
mysql
Adil Siddiqi
Adil Siddiqi

Reputation: 11

How to get integer from varchar data type using mysql function?

I want to write a function which return plan code when I give input plan title But my function not working please help me and correct my code. For example get_plan_tile_code(Break First) than it return me 1.I have also attached error file. I will be thankful to you.

Plan_code   Plan_title
1           Break First
2           Lunch
3           Dinner

id  Plan_title  Employee name
1   Break First   ABC
2   Lunch         DCF
3   Dinner        GHI


DELIMITER //
CREATE FUNCTION get_plan_tile_code(Actvi_SubPln_Title VARCHAR)RETURNS INT(99) 
BEGIN
DECLARE ATY_CODE INT(99) DEFAULT"";

  SELECT plan_id  INTO   ATY_CODE
  FROM   tbl_plan_scata
  WHERE  plan_sub_title = Actvi_SubPln_Title;
  
  RETURN ATY_CODE;
  
END //;
DELIMITER ;

enter image description here

Upvotes: 1

Views: 22

Answers (1)

slaakso
slaakso

Reputation: 9080

Pay attention to the datatype declarations. Your Actvi_SubPln_Title-parameter is defined as VARCHAR with no length (you will need to define the length).

The function return value and the ATY_CODE -variable in the function on the other hand have length defined on INT (this is not needed). And datatype INT should not have default "" as it is not a string.

Upvotes: 1

Related Questions