Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2013
    Posts
    2

    Question Unanswered: 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:

    $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
    Join Date
    Jun 2013
    Posts
    2
    Update: After much trying/testing, since this query is run by a php script in a browser, I've decided to not go with stored procedures...but use mysqli prepared statements instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •