How to use wildcards with parameter in stored procedure

Question

I have a stored procedure, which counts results from a query, with 2 in parameters. One needs to be an exact match, the other can match as long as it starts with the specified parameter.

CREATE OR REPLACE PROCEDURE CandidateCount(IN param_pack VARCHAR(64), IN param_value VARCHAR(64), OUT result INTEGER)
BEGIN
    SELECT COUNT(*) INTO result " + conditions + " LIMIT 1; 
END

conditions:

String conditions = " FROM Product WHERE ";
    conditions += "PackageName = RTRIM(param_pack)";    //works fine
    
    conditions += " AND (";

    conditions += "ProductNumber = RTRIM(param_value)";     //works fine
    conditions += " OR ";
    conditions += "ProductNumber LIKE RTRIM(param_value) + '%'";    //doesnt work
    conditions += " OR ";
    conditions += "ProductName LIKE RTRIM(param_value) + '%'";      //doesnt work
    
    conditions += ")";

I tried to do the stuff people said in this question.

I'm not getting any warnings about the syntax so I'm not sure what I'm doing wrong. The database I'm connecting to is MariaDB 10.3, and I'm doing so through jdbc.

score 0 · Accepted Answer · answered Oct 06 '22 at 03:05

In SQL + is an arithmetic operator. If you add strings using the + operator, the strings will be converted to a decimal number before the addition is performed.

MariaDB [(none)]> select "123" + "4";
+-------------+
| "123" + "4" |
+-------------+
|         127 |
+-------------+

If a string doesn't contain a number it's value will be converted to 0:

MariaDB [(none)]> select "foo" + "%";
+-------------+
| "foo" + "%" |
+-------------+
|           0 |
+-------------+

So instead using arithmetic operators for string operations, use the CONCAT() string function to concatenate strings:

MariaDB [(none)]> select concat("foo", "%");
+--------------------+
| concat("foo", "%") |
+--------------------+
| foo%               |
+--------------------+

score 0 · Answer 2 · answered Oct 06 '22 at 06:49

What you are looking for is dynamic SQL (you cannot just use +-operator to create a working query).

However, there is very little benefit for using stored procdure like that. You might as well generate the SQL query in your application layer without the procedure.

How to use wildcards with parameter in stored procedure

2 Answers2