Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: help writing a store procedure

    hi
    i want to pass a parameter(val) to a sp, & use it in sql query like this:
    Code:
    SELECT * FROM tblTest WHERE 1=1 AND status=val
    the thing that i want is when user pass -1 as value to sp, eecuted query omit where clauses (status=val), & sql statemnt became like this:
    Code:
    SELECT * FROM tblTest WHERE 1=1

  2. #2
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20
    If you want to create a query with data passed into a stored procedure, you have to concanate it with a string

    SET @queryString = concat("select * from `table` where `column`='",@inputVal,"'");
    PREPARE statement1 FROM @queryString;
    EXECUTE statement1;

    Alternatively, as long as you aren't dynamically changing table names/column names:
    SET @queryString = "select * from `table` where `column`=?";
    PREPARE statement1 FROM @queryString;
    EXECUTE statement1 USING @inputVal;

    ____________________
    Juacala - Web Application Developer, Eliacom, Inc.
    MySQL GUI Tools for Online Applications: Eliacom

Posting Permissions

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