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
– Fahad Anjum

2018-05-17 08:57:21 +00:00
Commented May 17, 2018 at 8:57 — Fahad Anjum
– Fahad Anjum, Commented May 17, 2018 at 8:57

P.Salmon · Accepted Answer · 2018-05-17 11:21:24Z

1

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

answered May 17, 2018 at 11:21

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

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

Comments

Thomas G · Accepted Answer · 2018-05-17 08:59:43Z

0

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;

answered May 17, 2018 at 8:59

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to Create Stored Procedure in MySQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related