Why doesn't the following stored procedure compile with MySQL Workbench version 6.3.?

Question

I would like to know how to make the following stored procedure compile with MySQL Workbench version 6.3.

use puppy;
DELIMITER $$
CREATE PROCEDURE spGetSystemLicensing
(
 IN _licenseName varchar(128)
)
BEGIN
IF (TRIM(_licenseName) = '')
 select * from SystemLicensing; 
ELSE
 select * from SystemLicensing where LicenseName=_licenseName;
END IF;
END

Here is my MySQL Workbench error message:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from SystemLicensing' at line 8

Any help is greatly appreciated.

Answer 1

You're just missing the keyword THEN. Read more about the correct syntax here.

Try it like this:

use puppy;
DELIMITER $$
CREATE PROCEDURE spGetSystemLicensing
(
 IN _licenseName varchar(128)
)
BEGIN
IF (TRIM(_licenseName) = '') THEN
 select * from SystemLicensing; 
ELSE
 select * from SystemLicensing where LicenseName=_licenseName;
END IF;
END $$
DELIMITER ;

• Frank: @tomborn, How could I reach you in the near future about MySQL technical questions? Thank you very much.
• tombom: You're welcome. When you post questions here, you usually get help. In the rare case, that noone answers, you can offer a bounty on your question. Then you will get an answer for sure.

Answer 2

I used the MySQL set theory operator UNION ALL to make the MySql stored procedure compile.

use puppy;
DELIMITER //
CREATE PROCEDURE spGetSystemLicensing
(
 IN arg char(128)
)
BEGIN
 SELECT * from SystemLicensing WHERE TRIM(arg) = ' '
 UNION ALL
 SELECT * from SystemLicensing where LicenseName = arg;
END //

Please leave a comment if you would like to talk through a MySQL Or Oracle question with me.

Answer 3

use puppy;
DELIMITER $$
CREATE PROCEDURE spGetSystemLicensing
(
 IN _licenseName varchar(128)
)
BEGIN
IF (TRIM(_licenseName) = '') THEN
 select * from SystemLicensing; 
ELSE
 select * from SystemLicensing where LicenseName=_licenseName;
END IF;
END $$
DELIMITER ;