Siamak Ferdos Siamak Ferdos - 4 years ago 138
MySQL Question

Simple IF sequence in MYSQL

I am working with creating a dynamic sql stored procedure in MYSQL. My problem is really simple but I couldn't run it after hours! Please help me if I've mistaked something.

DELIMITER //
CREATE PROCEDURE sp_job_list(
IN id VARCHAR(10) CHAR SET utf8,
IN job_title VARCHAR(50) CHAR SET utf8,
IN qualify VARCHAR(50) CHAR SET utf8
)
BEGIN
SET @s = 'SELECT * FROM wp_sho_jobrequirment WHERE 1 = 1 ';
IF job_title <> NULL AND job_title <> ''
THEN
SET @s = CONCAT(@s, ' AND job_title LIKE %', job_title, '%');
END IF;
IF qualify <> NULL AND qualify <> ''
THEN
SET @s = CONCAT(@s, ' AND qualify LIKE %', qualify, '%');
END IF;
IF id <> NULL AND id <> ''
THEN
SET @s = CONCAT(@s, ' AND id =', id);
END IF;

SELECT @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
DELIMITER ;


My main problem, I think, is with
if
statement where I didn't write
THEN END IF
clauses at first but it doesn't work with some compile error! Then when I put clauses I compiled but not work!
When I use this code to call the stored procedure IF statement not act so nothing append to my dynamic sql query:

Call SP:

SET @p0=''; SET @p1='f'; SET @p2='f'; CALL `sp_job_list`(@p0, @p1, @p2);


Dynamic Query String At Result:

SELECT * FROM wp_sho_jobrequirment WHERE 1 = 1

Answer Source

I think your results are due to incorrect usage of the <> operator against NULL.
<> means Not equal. You can't directly apply it on NULL values.
Instead use IS NOT to compare with NULL.

The statement

IF job_title <> NULL AND job_title <> ''

should be

IF job_title IS NOT NULL AND job_title <> ''

Similarly:

Change IF qualify <> NULL AND qualify <> ''
To IF qualify IS NOT NULL AND qualify <> ''

And

Change IF id <> NULL AND id <> ''
To IF id IS NOT NULL AND id <> ''

Refer to Documentation: Not Equal ( <> )

!= (or <>) doesn't see NULL as something that can be compared to and thus doesn't compare itself to those records that have the field marked null.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download