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

marc_s · Accepted Answer · 2016-01-23 08:49:00Z

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 queries and be quite efficient.

edited Jan 23, 2016 at 8:49

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 26, 2009 at 12:04

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Riyas · Accepted Answer · 2009-10-27 11:41:38Z

0

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

answered Oct 27, 2009 at 11:41

Riyas

3275 silver badges16 bronze badges

1 Comment

Quassnoi Over a year ago

You need to put keyid = 0 into the last query, not pkid = 0 like in my original query. This way getAllPortfoliosDemo(0) will return all records.

Collectives™ on Stack Overflow

mysql stored procedure with parameters problem

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related