1

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.

4

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 ;
  • @tomborn, How could I reach you in the near future about MySQL technical questions? Thank you very much. – Frank Jul 14 '16 at 13:39
  • 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. – tombom Jul 15 '16 at 6:49
0

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.

0
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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.