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 ;

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 ;