mysql stored procedure with parameters problem

Question

I had created following procedure.

DELIMITER ;;

DROP PROCEDURE IF EXISTS getAllPortfoliosDemo;;

CREATE PROCEDURE getAllPortfoliosDemo( IN keyid INT(10)) 
    BEGIN 
        DECLARE whereString char(100);

        IF (keyid > 0 ) THEN
            SET  whereString =  CONCAT( ' WHERE pkid = ', keyid );
        ELSE
            SET whereString = ' WHERE 1 ';
        END IF;


        SELECT pkid, title FROM Portfolio whereString;

    END ;;

this query returns no error. it works well without parameters. for eg CALL getAllPortfoliosDemo(); works. but it didn't works with a parameter. for eg CALL getAllPortfoliosDemo(5); didn't returns any row.

also i tried the following alternate query

DELIMITER ;;

DROP PROCEDURE IF EXISTS getAllPortfoliosDemo;;

CREATE PROCEDURE getAllPortfoliosDemo( IN keyid INT(10)) 
    BEGIN 
        DECLARE whereString char(100) DEFAULT NULL;

        IF (keyid > 0 ) THEN
            SET  whereString =  CONCAT( ' AND pkid = ', keyid );
        END IF;


        SET @SQLstmt =  CONCAT('SELECT pkid, title FROM Portfolio ', whereString) ;

        PREPARE SQLbase FROM @SQLstmt;
    EXECUTE SQLbase;
    DEALLOCATE PREPARE SQLbase;

    END ;;

this also didn't returns any result set. any one can a sagest a method. thanks in advance

Answer 1

SELECT pkid, title FROM Portfolio whereString;

This way your whereString variable gets cast into BOOLEAN and always evaluates to true, since it's not empty.

IF (keyid > 0 ) THEN
    SET  whereString =  CONCAT( ' AND pkid = ', keyid );
END IF;

SET @SQLstmt =  CONCAT('SELECT pkid, title FROM Portfolio ', whereString)

This should fail for values higher than 0, since it results in the following statement:

SELECT  pkid, title
FROM    Portfolio
WHERE   AND pkid = $keyid
--      ^
--  Wrong!

Just use the following statement:

SELECT  pkid, title
FROM    Portfolio
WHERE   pkid = keyid
UNION ALL
SELECT  pkid, title
FROM    Portfolio
WHERE   keyid = 0

This will optimize out one of the SELECT quieries and be quite efficient.

Answer 2

Yes. Quassnoi answered this question well. thanks a lot to him. I had modified the query as

DELIMITER ;;

DROP PROCEDURE IF EXISTS getAllPortfoliosDemo;;

CREATE PROCEDURE getAllPortfolios( IN keyid INT(10)) 
    BEGIN 
        DECLARE whereString char(100) DEFAULT NULL;

        IF (keyid > 0 ) THEN
            SET  whereString =  CONCAT( ' WHERE 1 AND pkid = ', keyid );
        ELSE
            SET  whereString =  ' WHERE 1 ';
        END IF;


        SET @SQLstmt =  CONCAT('SELECT pkid, title  FROM Portfolio ', whereString) ;

        PREPARE SQLbase FROM @SQLstmt;
 EXECUTE SQLbase;
 DEALLOCATE PREPARE SQLbase;

    END ;;

so that 'CALL getAllPortfoliosDemo(0)' will returns all records and 'CALL getAllPortfoliosDemo(5)' will returns fifth row

the query

  CREATE PROCEDURE getAllPortfoliosDemo( IN keyid INT(10)) 
    BEGIN 
       SELECT  pkid, title
       FROM    Portfolio
       WHERE   pkid = keyid
   UNION ALL
      SELECT  pkid, title
      FROM    Portfolio
      WHERE   pkid = 0;
    END ;;

is too useful. but 'CALL getAllPortfoliosDemo(0)' here retuns no record because we didn't have a record with primary key id '0'. getAllPortfoliosDemo(5) works fine