MySQL Forums
Forum List  »  Stored Procedures

Output variable always returns NULL
Posted by: Gabriel Harriman
Date: December 14, 2005 02:32PM

Hello everybody,

I'm new to creating storedprocs in MySQL and I've run into a wall. I've created a pretty simple stored procedure to return a delta or full list of users by calling additional sprocs based on the last time the application pulled data, determined by a timestamp updated on each call.

The sproc will return a result set and a single character output variable. The application knows if it's a delta or full list of users by using the output variable.

Here's the sproc code:

DELIMITER $$;

DROP PROCEDURE IF EXISTS `presence`.`pullUsers`$$

CREATE PROCEDURE `pullUsers`(my_client_id MEDIUMINT, OUT pullType CHAR(1))
BEGIN
DECLARE my_last_pull TIMESTAMP;

SELECT last_pull INTO my_last_pull
FROM scorecard
WHERE client_id = my_client_id;

IF @my_last_pull < date_add(now(), INTERVAL '-1' HOUR) THEN
SET pullType = 'f';
CALL pullUsersFull(my_client_id);
ELSE
SET pullType = 'd';
CALL pullUsersDelta(my_client_id, @my_last_pull);
END IF;
UPDATE scorecard
SET last_pull = NULL
WHERE client_id = my_client_id;

SET pullType = 'o';
END$$

DELIMITER ;$$

I've been writing the sprocs using SQLyog (sort of like Query Analyzer) and it never returns a value for "pullType". I messed around with the placement of setting this variable; notice the SET pullType = 'o'; at the very end. I set it there and I still get NULL back in the output variable.

I did a control test by creating a sproc took one parameter, the output variable, and it does in fact work that way. So, I know it's not a problem with how I'm calling it from SQLyog.

Here's how I run the sproc in SQLyog:

call pullUsers(4, @test);
select @test;

The two called sprocs in the code above both return a result set. They don't do anything with the "pullType" variable.

This is using MySQL v 5.0.16-nt-max via TCP/IP.

Any help would be greatly appreciated!!

Thanks,

Gabe

Options: ReplyQuote


Subject
Views
Written By
Posted
Output variable always returns NULL
5654
December 14, 2005 02:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.