MySQL stored procedure with variable argument

Question

I made a stored procedure. I want it to filter data by various parameters. If I pass one parameter, it should be filtered by one; if I pass two, it should be filtered by two and so on, but it does not work.

Can anybody help me?

DROP PROCEDURE IF EXISTS medatabase.SP_rptProvince2; CREATE PROCEDURE medatabase.`SP_rptProvince2`( IN e_Region VARCHAR(45) ) 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; PREPARE stmtp FROM @strQuery; EXECUTE stmtp; END; 

Answer 1

AFAIK, you cannot have such a list of variable arguments. You can do one of two things:

• Take a fixed maximum number of parameters and test them for null-ness before concatenating:

   CREATE PROCEDURE SP_rptProvince2(a1 VARCHAR(45), a2 VARCHAR(45), ...) ... IF a1 IS NOT NULL THEN SET @strQuery = CONCAT(@strQuery, ' AND ', a2); END IF; 

  If you need predefined fields to which criteria in the argument are applied (for example, the e_Region parameter in your existing code), then you modify the CONCAT operation accordingly.

  Possible call:

   CALL SP_rptProvince2('''North''', 'column3 = ''South''') 

• Take one parameter, which is much more than just 45 characters, and just add it to the query (if it is not null).

  Clearly, this puts a burden on the user to provide the correct SQL code.

  Possible call:

   CALL SP_rptProvince2('RegionName = ''North'' AND column3 = ''South''') 

You can not choose between them. Or you can make it work; none of them are completely satisfactory.

You may have noticed that it was necessary to protect the strings in the arguments with additional quotation marks; this is what makes this problem problematic.

Answer 2

I found a JSON based approach that works with the latest MySQL / MariaDB systems. Check out the link below (original author Federico Razzoli): https://federico-razzoli.com/variable-number-of-parameters-and-optional-parameters-in-mysql-mariadb-procedures

Essentially, you take the BLOB parameter, which is actually a JSON object, and then execute JSON_UNQUOTE (JSON_EXTRACT (json object, key)) depending on the situation.

I removed the statement here:

 CREATE FUNCTION table_exists(params BLOB) RETURNS BOOL NOT DETERMINISTIC READS SQL DATA COMMENT ' Return whether a table exists. Parameters must be passed in a JSON document: * schema (optional). : Schema that could contain the table. By default, the schema containing this procedure. * table : Name of the table to check. ' BEGIN DECLARE v_table VARCHAR(64) DEFAULT JSON_UNQUOTE(JSON_EXTRACT(params, '$.table')); DECLARE v_schema VARCHAR(64) DEFAULT JSON_UNQUOTE(JSON_EXTRACT(params, '$.schema')); IF v_schema IS NULL THEN RETURN EXISTS ( SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = v_table ); ELSE RETURN EXISTS ( SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = v_schema AND TABLE_NAME = v_table ); END IF; END;