#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3

    Question How to concatenate SQL within a stored procedure?


    Hi all,

    I'm converting embeded SQL statements (in php pages) to stored procedures.

    How does one concantenate a SQL select statement?

    With embeded, one might do something like this:

    PHP Code:
    $buildSQL "SELECT * FROM TABLE1 WHERE ";

    if (
    $var1 != "") {
       
    $buildSQL .= "Column1 = ".$var1;
    }

    if (
    $var2 != "") {
       
    $buildSQL .= " AND Column2 = ".$var2;
    }

    //etc. 
    Here is what I've got for the stored procedure (which returns all rows and not those limited to what the input vars are):

    DELIMITER $$

    CREATE DEFINER=`my_site`@`%` PROCEDURE `sp_basicSearch`(
    in sp_var1 tinyint(1), sp_var2 char(2)
    )
    BEGIN
    DECLARE query_full TEXT;

    SET @query_part = 'SELECT * FROM Table1 ';

    IF sp_var1 != null THEN
    SET @query_part = CONCAT(@query_part, 'WHERE Category = ', sp_var1, '\'');
    END IF;

    if sp_var2 != null THEN
    SET @query_part = CONCAT(@query_part, ' AND State = ', sp_var2, '\'');
    END IF;

    SET query_full = @query_part;

    SET @query_full = query_full;

    PREPARE STMT FROM @query_full;
    EXECUTE STMT;
    END
    It doesn't work. I know the query needs to be tweaked more if one or both vars are empty...but for now to get the idea on this works, i'm testing with both vars not null...which currently returns all rows, even those that are not equal to the input vars.

    How do I get the concatenation right?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Update: After trying/testing/reading more...since this query will be run by a php script in a browser, I've decided I'll go with mysqli prepared statements instead of a stored procedure.

IMN logo majestic logo threadwatch logo seochat tools logo