Thread: Using Parameterized Stored Procedure

RR;
I didn't take the time to unravel all the logic in your SP, but it appears in mySQL that you can PREPARE..EXECUTE a query whilst substituting parameters. In you case, consider if you can build your dynamic query in such a way to use parameters in the EXECUTE statement rather than building them into the query. Such as the very simple example:

Code:

     SET @s = 'SELECT * FROM authors WHERE id BETWEEN ? AND ?'; 
    SET @min = _min; 
    SET @max = _max; 

    PREPARE stmt1 FROM @s; 
    EXECUTE stmt1 USING @min, @max; 
    DEALLOCATE PREPARE stmt1;

Yeah, now I see what you mean. I was trying to figure out a way to do this while having the same parameters in the same position regardless of query -- but that's going to make for some really inefficient queries. Take the rather simple example of your conveyor predicate:

Code:

IF iconveyor <> '' AND iconveyor<>'all' THEN
 SET _conveyor = ' AND visitors.conveyor = ?';
ELSE
 SET _conveyor = ' AND visitors.conveyor LIKE ?;
 SET @iconveyor = '%';
END IF;

Consider all the permutations, and you could have a ton of LIKE's in the query. Gross