CODEWITHSUNDEEP
mysqlstored-procedures
inba
inba

Reputation: 45

How to use IF condition in Stored Procedure Query

Here is my Stored procedure Query. Here if typee is existing it should add the 2nd condition or it should not add. My table structure is like this 

|id | name | typee |is_primary|
|1  |IND   |0      |1         |
|2  |SRIL  |1      |0         |

My Stored Procedure query is following 

CREATE DEFINER=`root`@`localhost` PROCEDURE `testIfCondition`(IN countryId int(11),IN typee INT(11),IN is_primary INT(11),
out name VARCHAR(50))
BEGIN
     select name from country where id = id
        if typee != 0 then
          and typee= typee 
        end if;
       if is_primary !=0 then
          and primary = primary 
        end if;
END

In the above query which ever is having values has to get execute. If anyone know please help me out in this. When i am trying it is giving some syntax error. Please tell me correct syntax to solve my issue.

Thanks in advance !!!!

Upvotes: 1

Views: 3729

Answers (1)

Kyojimaru
Kyojimaru

Reputation: 2724

I don't think that you could add more condition using the IF statement. You can probably select it twice or more and add it to a temporary table, for example like this:

First create the temporary table

DECLARE @tempTable TABLE (
    ID INT,
    Name NVARCHAR(250),
    Typee INT
)

Then insert the first SELECT result to the @tempTable

INSERT INTO @tempTable
SELECT * FROM country WHERE id = countryId

Then use the condition to check if the typee is NULL or not

IF typee IS NOT NULL AND is_primary IS NOT NULL THEN
    SELECT * FROM @tempTable WHERE typee = typee AND is_primary = is_primary
ELSE IF typee IS NOT NULL THEN
    SELECT * FROM @tempTable WHERE typee = typee
ELSE IF is_primary IS NOT NULL THEN
    SELECT * FROM @tempTable WHERE is_primary = is_primary
ELSE
    SELECT * FROM @tempTable
END

though I think this is not the best practice to do it.

EDIT:

Based on the other answer here, you could create the query into a string first, then execute it. Your sp should look like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `testIfCondition`(IN countryId int(11),IN typee INT(11),IN is_primary INT(11),
out name VARCHAR(50))
BEGIN
    SET @query = CONCAT('select name from country where id = ', countryId);
    if typee <> 0 then
        SET @query = CONCAT(@query, ' AND typee = ', typee);
    end if;
    if is_primary <> 0 then
        SET @query = CONCAT(@query, ' AND is_primary = ', is_primary);
    end if;

    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

You can look here to see the execute query from the string.

Upvotes: 1

Related Questions