Declaring INT variable to return in MYSQL Function problem

Question

I'm trying to build a mysql function which inserts values into a table and returns me the last_id inserted, which should be the same after executing the insertion. But Xampp gives me an error in the line where im declaring the "last_bill_id" variable. Can someone please help me to understand what am I doing wrong?

Here is the code for the function:

CREATE FUNCTION insert_bill(

        client_id varchar (12),
        bill_date date
    ) RETURNS INT
    BEGIN
        DECLARE last_bill INT; 
        INSERT INTO bill
        (
            client_id, bill_date
        )
         VALUES
        (
            client_id, bill_date
        );    
        SET last_bill = LAST_INSERT_ID();
        RETURN last_bill;  
    END $$
    DELIMITER ;

Error: #1064 - Something is wrong about 'INT

nbk · Accepted Answer · 2021-03-15 22:28:26Z

2

you need another DELIMITER at the start

DELiMiTER $$
CREATE FUNCTION insert_bill(

    client_id varchar (12),
    bill_date date
) RETURNS INT
DETERMINISTiC
BEGIN
    DECLARE last_bill INT; 
    INSERT INTO bill
    (
        client_id, bill_date
    )
     VALUES
    (
        client_id, bill_date
    );    
    SET last_bill := LAST_INSERT_ID();
    RETURN last_bill;  
END $$
DELIMITER ;

answered Mar 15, 2021 at 22:28

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

2 Comments

Clown Over a year ago

Thanks a lot , the function got created, but when I execute this: SELECT insert_bill('111111111', STR_TO_DATE('18-03-2020', '%d-%m-%Y'); it gives me the same error

Clown Over a year ago

I removed the "DETERMINISTIC" and it worked, for my query: SELECT insert_bill2('111111111', STR_TO_DATE('14-03-2020', '%d-%m-%Y'))

Collectives™ on Stack Overflow

Declaring INT variable to return in MYSQL Function problem

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related