Problem creating Stored Procedure in MySQL

Question

I have written the below mentioned script in MySQL to create Stored Procedure:

CREATE PROCEDURE `AddBranch`(
IN `inCompanyCode` char(3), 
IN `inBranchCode` varchar(6), 
IN `inBankBranch` varchar(40)
)
BEGIN
    DECLARE branchExists TINYINT DEFAULT 0;
    SELECT Count(*) INTO branchExists FROM branches WHERE CompanyCode = inCompanyCode AND BranchCode = inBranchCode;
    IF branchExists = 0 THEN
        INSERT INTO branches VALUES (inCompanyCode, inBranchCode, inBankBranch);
    ELSE
        UPDATE branches SET Branch = inBankBranch
        WHERE CompanyCode = inCompanyCode AND BranchCode = inBranchCode;
    END IF;
END;

While running the query, the error message displayed is:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 7

I have even tried DECLARE branchExists TINYINT(1) DEFAULT 0; but the problem persists.

What is wrong with line 7?

Regards

Madhur Bhaiya · Accepted Answer · 2018-09-23 09:16:18Z

1

You need to redefine Delimiter to something else (eg: $$), instead of (;).
Also as a safety measure, check if the same name procedure already exists or not (DROP PROCEDURE IF EXISTS)
At the end, redefine the DELIMITER to ;

Change the stored procedure to as follows:

DELIMITER $$
DROP PROCEDURE IF EXISTS `AddBranch`$$

CREATE PROCEDURE `AddBranch`(
IN `inCompanyCode` char(3), 
IN `inBranchCode` varchar(6), 
IN `inBankBranch` varchar(40)
)
BEGIN
    DECLARE branchExists TINYINT DEFAULT 0;
    SELECT Count(*) INTO branchExists FROM branches WHERE CompanyCode = inCompanyCode AND BranchCode = inBranchCode;
    IF branchExists = 0 THEN
        INSERT INTO branches VALUES (inCompanyCode, inBranchCode, inBankBranch);
    ELSE
        UPDATE branches SET Branch = inBankBranch
        WHERE CompanyCode = inCompanyCode AND BranchCode = inBranchCode;
    END IF;
END$$

DELIMITER ;

answered Sep 23, 2018 at 9:16

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

2 Comments

Kumar Kush Over a year ago

Thanks it worked. But could you please tell me why do we need $$?

Madhur Bhaiya Over a year ago

@kush.impetus $$ is just an example. It could be anything like ??. To create a stored procedure, we have to run the above query. And, we want this whole statement to get stored as a SP, together. But, if we have ; in between, MySQL parser will try to execute statements individually, which wont make any sense (instead of storing it as a SP). So, we set the delimiter to something else, so that it gets stored properly.

Collectives™ on Stack Overflow

Problem creating Stored Procedure in MySQL

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