1

Pass the parameters in several ways also do not have data. How do I write a query in a procedure? (SET datatype parameter)

CREATE TABLE tb (
   iCode INT(11) NOT NULL DEFAULT 0 COMMENT 'Primary Key'
 , bChk1 VARCHAR(1) NOT NULL DEFAULT 'Y' COMMENT '(Y, N)'
 , bChk2 SET('Y', 'N') DEFAULT 'Y' COMMENT '(Y, N)'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE PROCEDURE getChk1 (
  IN inChk VARCHAR(10)
)
BEGIN
  SELECT * FROM tb WHERE bChk1 IN (inChk);
END;

CREATE PROCEDURE getChk2 (
  IN inChk SET('Y', 'N') 
)
BEGIN
  SELECT * FROM tb WHERE bChk2 IN (inChk);
END;


## Row Data
# ----------------------
# iCode ｜ bChk1 ｜ bChk2
# ----------------------
#   1   ｜   Y   ｜   Y
#   2   ｜   N   ｜   Y
#   3   ｜   Y   ｜   N
#   4   ｜   N   ｜   N
# ----------------------

Execute procedure...

CALL getChk2 ("'Y', 'N'");   -- No Data
CALL getChk2 ('"Y", "N"');   -- No Data
CALL getChk2 ('1, 2');       -- No Data
CALL getChk2 ("'1', '2'");   -- No Data
CALL getChk2 ("'Y'｜'N'");    -- No Data
CALL getChk2 ('Y｜N');        -- No Data
CALL getChk2 ("'1'｜'2'");    -- No Data
CALL getChk2 ('1｜2');        -- No Data

CALL getChk1 ("'Y', 'N'");   -- No Data
CALL getChk1 ('"Y", "N"');   -- No Data
CALL getChk1 ("'Y'｜'N'");    -- No Data

How do I write a query in a procedure? (SET datatype parameter) Thanks for reading.

devcorea
  • 21
  • 4

2 Answers2

1

Pass array to MySQL stored routine

DROP PROCEDURE IF EXISTS getChk1;
DELIMITER //
CREATE PROCEDURE getChk1(IN inChk VARCHAR(10))
BEGIN
  SET @sql = CONCAT('SELECT * FROM tb WHERE bChk1 IN (', inChk, ')');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;
CALL getChk1 ('\'Y\', \'N\'');
Community
  • 1
  • 1
Oleg
  • 67
  • 2
1

Thank Him who answers. Tried to resolve queries into static, not dynamic query.

CREATE PROCEDURE getChk2 (
  IN inChk SET('Y', 'N') 
)
BEGIN
  SELECT * FROM tb WHERE FIND_IN_SET(bChk2, inChk);
END;
CALL getChk2 ('Y,N');
devcorea
  • 21
  • 4