How to Create Stored Procedure in MySQL

Question

My tables are

create table employee(
id int(10) auto_increment primary key,
name varchar(100),
addressId int(10)
);
go
create table address(
id varchar(10) auto_increment primary key,
name varchar(100)
);

Here is my procedure

create procedure insert_employee(IN emp_name varchar(100),IN emp_address varchar(100))
begin


    DECLARE @addressId varchar(10);
    SELECT @addressId:=id from address where name LIKE '%'+emp_address+'%';


    IF @addressId = ''
    THEN 
        set @addressId= 'DBS-2136';-- It will come form function
        INSERT INTO address values(@addressId,emp_address);
    END IF

    INSERT INTO employee values(emp_name,@addressId);
END

I don't understand what is the problem. If i write this type of if condition in ms sql server there is not error. every time execute the procedure ti say error in end if. I have search in google but there is not idea about this. there is a problem in declare variable. If i copy form mysql documentation that also not work. why is that? please help me 1. What is the proper way to declare variable under mysql stored procedure, 2. how to write if condition in mysql stored procedure. thank you

Try adding semicolon after end if. – Fahad Anjum May 17 '18 at 08:57 — Fahad Anjum, May 17 '18 at 08:57

score 1 · Answer 1 · answered May 17 '18 at 11:21

Lots of differences between mysql and mssql. Declared variables should not include '@', all statements must be terminated, + is an arithmetic operator, if you procedure has multiple statements you must set delimiters before and after. Further reading How to declare a variable in MySQL? https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html

score 0 · Accepted Answer · answered May 17 '18 at 08:59

From MySQL reference Manual

An IF ... END IF block, like all other flow-control blocks used within stored programs, must be terminated with a semicolon

IF @addressId = ''
THEN 
    set @addressId= 'DBS-2136';-- It will come form function
    INSERT INTO address values(@addressId,emp_address);
END IF;

How to Create Stored Procedure in MySQL

2 Answers2