MySQL stored procedure with variable argument list

Question

I have made a stored procedure. I want it to filter the data by different 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 is not working.

Can anyone help me please?

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

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

Basically, you take a BLOB parameter which is actually a JSON object and then do JSON_UNQUOTE(JSON_EXTRACT(json object, key)) as appropriate.

Lifted an extract 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;

Answer 2

AFAIK, you can't have a variable argument list like that. You can do one of a couple of things:

1. Take a fixed maximum number of parameters, and check 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 predetermined fields to which the criteria in the argument apply (like the e_Region parameter in your existing code), then you modify the CONCAT operation appropriately.

   Possible invocation:

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

2. Take a single parameter that is much bigger than just 45 characters, and simply append it to the query (assuming it is not null).

   Clearly, this places the onus on the user to provide the correct SQL code.

   Possible invocation:

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

There's not a lot to choose between the two. Either can be made to work; neither is entirely satisfactory.

You might note that there was a need to protect the strings in the arguments with extra quotes; that is the sort of thing that makes this problematic.