Stored Procedure - OUT param returns null when selected

Question

I have a MySQL stored procedure with several IN params and one OUT.

DELIMITER $$
DROP PROCEDURE IF EXISTS create_or_update_post;
CREATE PROCEDURE create_or_update_post(IN user_login_id INT, IN postid VARCHAR(50), IN subject VARCHAR(150), IN postdate VARCHAR(50), IN body LONGTEXT, IN slug VARCHAR(50), OUT wp_post_id VARCHAR(50))
BEGIN
    IF EXISTS (SELECT @ID := `ID` FROM `wp_posts`, `wp_postmeta` WHERE `ID` = `post_id` AND `meta_key` = 'old_cs_id' AND `meta_value` = postid) THEN
        UPDATE `wp_posts`
        SET `post_title`=subject, `post_content`=body, `post_date`=postdate, `post_name`=slug, `post_author`=user_login_id, `post_modified`=(SELECT NOW())
        WHERE `ID` = @ID;
        SET wp_post_id = @ID;
    ELSE
        INSERT INTO `wp_posts` (`post_title`, `post_content`, `post_excerpt`, `to_ping`, `pinged`, `post_content_filtered`, `post_date`, `post_name`, `post_author`, `post_modified`, `post_status`)
        VALUES (subject, body, '', '', '', '', postdate, slug, user_login_id, (SELECT NOW()),'draft');

        /* Create metadata */
        SET wp_post_id = LAST_INSERT_ID();

        INSERT INTO `wp_postmeta` (`post_id`,`meta_key`,`meta_value`) VALUES(wp_post_id, 'old_cs_id', postid);

    END IF;
END$$
DELIMITER ;

When I call the procedure and try to SELECT the variable, I get null.

CALL create_or_update_post(@user_login_id, '123', 'Some title', '2010-09-03 12:39:00', 'Some content', 'Blog Post', @wp_post_id);
SELECT @wp_post_id AS get_returned_post;

Returns:

@wp_post_id
NULL

Why is my OUT param always NULL?

Can you please share some example data and what output you are expecting. By just looking at query and without data I can only guess : "May be nothing is selected " — Varun Verma, Jan 07 '15 at 03:33
Are you sure that the `IF EXISTS` condition returns true? If not, can you be sure that `LAST_INSERT_ID()` returns a non-null value? — Rachcha, Jan 07 '15 at 06:16

score 0 · Answer 1 · answered Jan 07 '15 at 06:23

0

Try this:

SET @wp_post_id = 0;
CALL create_or_update_post('abc@abc.com', '123', 'Some title', '2010-09-03 12:39:00', 'Some content', 'Blog Post', @wp_post_id);
SELECT @wp_post_id AS get_returned_post;

answered Jan 07 '15 at 06:23

Saharsh Shah

28,687
8
48
83

RNickMcCandless · Accepted Answer · 2015-01-07T21:09:02.623

This is what happens when a programmer tries to create a MySQL stored procedure. This issue was a result from trying to set the @ID variable from a query in the IF EXISTS statement.

I rewrote the following and now I am able to get the wp_post_id.

DELIMITER $$
DROP PROCEDURE IF EXISTS create_or_update_post;
CREATE PROCEDURE create_or_update_post(IN user_login_id INT, IN postid VARCHAR(50), IN subject VARCHAR(150), IN postdate VARCHAR(50), IN body LONGTEXT, IN slug VARCHAR(50), OUT wp_post_id VARCHAR(50))
BEGIN
    IF EXISTS (SELECT * FROM `wp_posts`, `wp_postmeta` WHERE `ID` = `post_id` AND `meta_key` = 'old_cs_id' AND `meta_value` = postid) THEN
        SELECT @ID := `ID` FROM `wp_posts`, `wp_postmeta` WHERE `ID` = `post_id` AND `meta_key` = 'old_cs_id' AND `meta_value` = postid;
        UPDATE `wp_posts`
        SET `post_title`=subject, `post_content`=body, `post_date`=postdate, `post_name`=slug, `post_author`=user_login_id, `post_modified`=(SELECT NOW())
        WHERE `ID` = @ID;
        /* Update categories */
        SET wp_post_id = @ID;
    ELSE
        INSERT INTO `wp_posts` (`post_title`, `post_content`, `post_excerpt`, `to_ping`, `pinged`, `post_content_filtered`, `post_date`, `post_name`, `post_author`, `post_modified`, `post_status`)
        VALUES (subject, body, '', '', '', '', postdate, slug, user_login_id, (SELECT NOW()),'draft');

        /* Create metadata */
        SET wp_post_id = LAST_INSERT_ID();

        INSERT INTO `wp_postmeta` (`post_id`,`meta_key`,`meta_value`) VALUES(wp_post_id, 'old_cs_id', postid);
    END IF;
END$$
DELIMITER ;

*Notice the change I made by duplicating the query in the IF EXISTS statement and setting the @ID within.

Stored Procedure - OUT param returns null when selected

2 Answers2

Linked