Stored FUNCTION is not returning result in MySql?

Question

I have a simple MySql query which successfully returns single value.

select tl.tour_log_id 
from tour_log tl
WHERE tl.log = "SUBTOUR_START" 
      AND tl.inquiry_id = 7519618
     and tl.truck_id = 450 and tl.tour_id = 6174
 limit 1; -- tour_log_id -> 736318. Even without limit 1, query gives always single value. This is how database is structured.

However, I do have a Mysql Stored Function, which is supposed to do the same thing, but I'm getting null. I generated this function by doing Right Click on Functions -> Create Function.

CREATE DEFINER=`root`@`localhost` FUNCTION `getTourLogIdForSubtourStart`( 
     inquiryId int, truckId int, tourId int) RETURNS int
        DETERMINISTIC
    BEGIN
        DECLARE tourLogIdSubtourStart int; 
        DECLARE tourLogIdSubtourEnd int;  
        
        select tour_log.tour_log_id into tourLogIdSubtourStart
        from fleaty.tour_log tl
        WHERE tl.log = "SUBTOUR_START" 
              AND tl.inquiry_id = inquiryId
             and tl.truck_id = truckId and tl.tour_id = tourId
         limit 1;    -- 
    
        -- set tourLogIdSubtourEnd = callSomeOtherFunction(tourLogIdSubtourStart, inquiryId, truckId);
         
        -- here will be cursor to process some result set, based on tourLogIdSubtourStart and tourLogIdSubtourEnd
    
        
        
    RETURN  (tourLogIdSubtourStart);
    END

This is how I call above function:

set @s = getTourLogIdForSubtourStart(7519618, 450, 6174);

select @s;

This prints null. Why?

Edit the question and add DDL and DML with some sample data for the table so that we can reproduce the issue. — sticky bit
– sticky bit, Commented Jun 19, 2021 at 16:50
As you correctly observe, LIMIT 1 is fine here. But note that in general, LIMIT without ORDER BY is fairly meaningless — Strawberry
– Strawberry, Commented Jun 20, 2021 at 5:20

nbk · Accepted Answer · 2021-06-19 16:55:57Z

2

Quite simply put never use column names as variable names

CREATE tABLE tour_log (tour_log_id int, log varchar(19),inquiry_id BIGint,truck_id int, tour_id int)

INSERT INTO tour_log VALUEs (1,'SUBTOUR_START',7519618, 450, 6174)

  CREATE  FUNCTION `getTourLogIdForSubtourStart`( 
     _inquiryId int, _truckId int, _tourId int) RETURNS int
        DETERMINISTIC
    BEGIN
        DECLARE tourLogIdSubtourStart int; 
        DECLARE tourLogIdSubtourEnd int;  
        
        select tour_log_id into tourLogIdSubtourStart
        from tour_log tl
        WHERE tl.log = "SUBTOUR_START" 
              AND tl.inquiry_id = _inquiryId
             and tl.truck_id = _truckId and tl.tour_id = _tourId
         limit 1;    -- 
    
        -- set tourLogIdSubtourEnd = callSomeOtherFunction(tourLogIdSubtourStart, inquiryId, truckId);
         
        -- here will be cursor to process some result set, based on tourLogIdSubtourStart and tourLogIdSubtourEnd
    
        
        
    RETURN  (tourLogIdSubtourStart);
    END

set @s = getTourLogIdForSubtourStart(7519618, 450, 6174);

select @s;

✓

| @s |
| -: |
|  1 |

SELECT * FROM tour_log

tour_log_id | log           | inquiry_id | truck_id | tour_id
----------: | :------------ | ---------: | -------: | ------:
          1 | SUBTOUR_START |    7519618 |      450 |    6174

db<>fiddle here

answered Jun 19, 2021 at 16:55

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wrapper Over a year ago

Thanks nbk. This is my first stored function tho :) I haven't tested it yet. As soon as I do, I'll upwote ofc.

Collectives™ on Stack Overflow

Stored FUNCTION is not returning result in MySql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related