CODEWITHSUNDEEP
mysqlstored-procedures
amin gul
amin gul

Reputation: 131

Having Issue with MySQL Stored Procedure

In below procedure I have two parameters - when I run should fill both parameters. I want these parameters optional if i fill first data should be filtered on base of one parameter if i fill the secand one and leave null the first one data should be filtered on base of secand parameter if i passed both parameters it should be filter based on both parameters if i didn't pass a parameter it should not be filtered.

DROP PROCEDURE IF EXISTS medatabase.SP_rptProvince2;
CREATE PROCEDURE medatabase.`SP_rptProvince2`(
IN e_Region VARCHAR(500),
IN e_Province VARCHAR(500)
)
BEGIN

  DECLARE strQuery VARCHAR(1024);
  DECLARE stmtp   VARCHAR(1024);


    SET @strQuery = CONCAT('SELECT * FROM alldata where 1=1 ');
   -- IF e_region IS NOT NULL THEN


    -- SET @strQuery = CONCAT(@strQuery, 'AND regionName'=e_Region);
   -- END IF;

    IF e_Region IS NOT NULL THEN
        SET @strQuery = CONCAT(@strQuery, ' AND regionName = "',e_Region,'"');


    IF e_Province IS NOT NULL THEN
        SET @strQuery = CONCAT(@strQuery, ' AND provinceName = "',e_Province,'"');

   END IF;
   END IF;

  PREPARE  stmtp FROM  @strQuery;
  EXECUTE  stmtp;
 End;

Upvotes: 0

Views: 1117

Answers (1)

eggyal
eggyal

Reputation: 125865

MySQL procedures do not support optional parameters. You have to do what you're currently doing (passing in and testing for NULL).

Upvotes: 2

Related Questions