CODEWITHSUNDEEP
mysqlstored-procedures
Zafar
Zafar

Reputation: 105

Check if row exists using Mysql store procedure not working

At mysql command prompt, I run:

SELECT EXISTS (SELECT 1 FROM test1 WHERE name1 like '%Nadiya%')

returns

EXISTS (SELECT 1 FROM test1 WHERE name1 like '%Nadiya%')
1

but following procedure doesn't print as Exists. I need to get inside IF statement. Please let me know what change could get me inside IF.

DELIMITER //
CREATE PROCEDURE verifyAndUpdate30(in searchName  varchar(12), in searchId   bigint, inout result int)
BEGIN
    IF ( SELECT EXISTS (SELECT 1 FROM test1 WHERE name1 like '%searchName%') ) THEN 
        SELECT 'EXISTS';
        UPDATE TEST SET testFlag=1 WHERE id=searchId;
        SET result=1;
    ELSE
        SELECT 'DOES NOT EXISTS';
    END IF; 

    SELECT result;
END 
//
DELIMITER ;

Call to procedure:

SET @increment = 0;
call verifyAndUpdate30('Nadiya', 5532, @increment);

Upvotes: 2

Views: 19107

Answers (2)

Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167212

You need to concatenate.

Since searchName can be taken as hardcoded string, replace your query this way:

CONCAT('%', searchName, '%')

Full Code

DELIMITER //
CREATE PROCEDURE verifyAndUpdate30(in searchName  varchar(12), in searchId   bigint, inout result int)
BEGIN
    IF ( SELECT EXISTS (SELECT 1 FROM test1 WHERE name1 like CONCAT('%', searchName, '%')) ) THEN 
        SELECT 'EXISTS';
        UPDATE TEST SET testFlag=1 WHERE id=searchId;
        SET result=1;
    ELSE
        SELECT 'DOES NOT EXISTS';
    END IF; 

    SELECT result;
END 
//
DELIMITER ;

See more about CONCAT in MySQL.

Upvotes: 4

Bohemian
Bohemian

Reputation: 425208

You need to construct the LIKE string the hard way, using CONCAT('%', searchName, '%'):

,,,
IF ( SELECT EXISTS (
    SELECT 1
    FROM test1
    WHERE name1 like CONCAT('%', searchName, '%')   -- Build %searchName% from parts
 ) ) THEN 
,,,

Upvotes: 4

Related Questions