CODEWITHSUNDEEP
mysqlsqldatabase
mapage2
mapage2

Reputation: 96

MySQL LIKE keyword not returning expected result

I have a stored procedure that I used with a search function I created. The query within the procedure is as follows:

    CREATE DEFINER=`root`@`localhost` PROCEDURE `advanced_search`(IN major_name VARCHAR(45), 
OUT major_code INT, IN classification VARCHAR(45), IN job_class VARCHAR(45))
BEGIN
SELECT major_id INTO major_code FROM major 
WHERE major = major_name;

SET @classification = classification;
SELECT * FROM job WHERE (major = major_code) OR (classification LIKE '%@classification%') OR (job_type = job_class);
END

I am having a problem with LIKE keyword clause. I want to check if a value in the classification field contains or includes the @classification variable. For some reason when I single out the clause that uses that keyword (by removing the first and third clause in the WHERE statement) it does not return anything.

Is there any advice on how to fix this?

Also, is it common for the object that appears below to be found within the results object?

    {
  "fieldCount": 0,
  "affectedRows": 0,
  "insertId": 0,
  "serverStatus": 34,
  "warningCount": 0,
  "message": "",
  "protocol41": true,
  "changedRows": 0
}

EDIT: Here is the query I call 

set @major_name = 'Chemical Engineering';
set @major_code = 0;
set @classification = 'Freshman';
set @job_class = 'Assistant';
call aggie_soop.advanced_search(@major_name, @major_code, @classification, @job_class);

Search Results after running procedure

These are the results. This is the exact same as the original table

Upvotes: 3

Views: 709

Answers (2)

spencer7593
spencer7593

Reputation: 108370

I don't think you need a user-defined variable, you can reference the procedure argument.

You can use the MySQL CONCAT() function to concatenate the percent sign literals with the value.

I prefer to qualify all column references, and use a naming convention for procedure variables that avoids name collisions. While MySQL doesn't care, it makes it much easier for the future reader to decipher the intent.

CREATE DEFINER=`root`@`localhost`
PROCEDURE `advanced_search`
( IN  p_major_name     VARCHAR(45)
, OUT p_major_code     INT
, IN  p_classification VARCHAR(45)
, IN  p_job_clas       VARCHAR(45)
)
BEGIN
  SELECT m.major_id INTO p_major_code
    FROM major m
   WHERE m.major = p_major_name
   LIMIT 1
  ;
  SELECT j.*
    FROM job j
   WHERE (j.major = p_major_code)
      OR (j.classification LIKE CONCAT('%',p_classification,'%')
      OR (j.job_type = p_job_class)
  ;
END

(I believe it's possible to "read" the value of the OUT parameter within the procedure, but I've never done it before.)

Note that if the p_classification string is a zero length string, then every row with a non-NULL value in the classification column is going to be returned.

You may want something like this:

      OR (p_classification <> '' AND j.classification LIKE CONCAT('%',p_classification,'%')

(But the specification isn't clear.)

Upvotes: 1

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

A small change is needed:

 CREATE DEFINER=`root`@`localhost` PROCEDURE `advanced_search`(IN major_name VARCHAR(45), 
OUT major_code INT, IN classification VARCHAR(45), IN job_class VARCHAR(45))
BEGIN
SELECT major_id INTO major_code FROM major 
WHERE major = major_name;

SET @classification = classification;
SELECT * FROM job WHERE (major = major_code) OR (classification LIKE '%' || @classification || '%') OR (job_type = job_class);
END

Upvotes: 0

Related Questions