Prepared Statement in Stored Procedure not returning rows to app 1

[VBRookie]

Hey guys,

I have a stored procedure that I'm using to allow users to perform a search on the database. I'm compiling the select statement in the stored procedure and then using PREPARE, EXECUTE, DEALLOCATE.

When I run just the stored procedure it works. When I call it from the app it returns 0 rows. If I just put the SQL statement without using PREPARE it returns rows. Here is the DDL for my stored procedure:

CREATE PROCEDURE `usp_contact_search`(firstName VARCHAR(25), lastName VARCHAR(25))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
     DECLARE search_query VARCHAR(500);
     DECLARE stmt_expr VARCHAR(500);
     DECLARE from_expr VARCHAR(500);
     DECLARE where_expr VARCHAR(500);

     SET stmt_expr = 'SELECT * FROM';
     SET from_expr = ' contact_general_info AS CGI, contact_address AS CA, contact_phone AS CP';
     SET where_expr = ' where CGI.contact_id = CA.contact_id AND CGI.contact_id = CP.contact_id AND CP.phone_id = 1';
     
     IF NOT ISNULL(firstName) THEN
        SET firstName = REPLACE(firstName, "'", "");
        SET where_expr = CONCAT(where_expr, " AND CGI.first_name LIKE '", LEFT(firstName, 3), "%'");
     END IF;
     
     IF NOT ISNULL(lastName) THEN
        SET lastName = REPLACE(lastName, "'", "");
        SET where_expr = CONCAT(where_expr, " AND CGI.last_name LIKE '", LEFT(lastName, 3), "%'");
     END IF;
     
     SET @search_query = CONCAT(stmt_expr, from_expr ,where_expr);
-- SELECT @search_query;
     PREPARE stmt FROM @search_query;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
END;

When I enter my paraments the following sql statement is generated and executed:

SELECT * FROM contact_general_info AS CGI, contact_address AS CA, contact_phone AS CP where CGI.contact_id = CA.contact_id AND CGI.contact_id = CP.contact_id AND CP.phone_id = 1 AND CGI.first_name LIKE 'How%';

When I comment out all the other code in my SP and just put the above statement in there and compile and run ... it returns 1 row to my app which is correct. But when I use PREPARE, EXECUTE, AND DEALLOCATE it returns 0 rows to my app.

Am I doing something wrong? How do I get the returned rows when using PREPARE?

Many Thanks,
- VB Rookie

 

[KarveR]

If it works bareback, then I'd be checking if the variable are actually passed to the select statement correctly, any way of displaying the select statement you are running from your app for debugging?

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[VBRookie]

DOH! I'm such an idiot ... I was writing the values out to the browser but I wasn't checking to see what was happening once they got into the stored proc. That's where the problem was hiding.

Thanks so much KarveR ... here's a star!

- VB Rookie

 

[KarveR]

glad to be of assistance

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.