User_T User_T - 4 months ago 7
MySQL Question

MySql procedure IF number is odd or even

My learning mysql and my question is to check what i'm doing wrong here:

I'm trying to make a sql procedure to check if the first number of a number user enters is odd or even but im getting syntax error in line 9 near ';//'

Here's the sql:

MYSQL>
DELIMITER //

CREATE PROCEDURE num()
BEGIN
IF (SELECT LEFT(num,1))=1 OR (SELECT LEFT(num,1))=3 OR (SELECT LEFT(num,1))=5 OR (SELECT LEFT(num,1))=7 THEN
SELECT 'number is odd';
ELSEIF (SELECT LEFT(num,1))=2 OR (SELECT LEFT(num,1))=4 OR (SELECT LEFT(num,1))=6 OR (SELECT LEFT(num,1))=8 THEN
SELECT 'number is even';
END IF;

END;//


And here is the CALL of then number for testing:

MYSQL> CALL num(3123123123)


Any ideas?

Answer

what you want to do is a calculation. % 2 will give the remainder of a division by 2. if that remainder is not 0 then it is odd

SELECT IF(LEFT(num, 1) % 2 <> 0, "number is odd", "number is even")

you also want to fix your procedure to something like this

DELIMITER //
CREATE PROCEDURE `num`( IN input_num int, OUT output varchar(200))
    BEGIN
        SELECT IF(LEFT(input_num, 1) % 2 <> 0, "number is odd", "number is even") INTO output;
    END//

you would call it like this.

set @a = '';
call num(333, @a);

select @a;

demo fiddle