User_T - 1 year ago 43

MySQL Question

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 Source

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