Save value of PREPARE into a variable

Jassim Rahma

Hi, I have below code:

WHILE i < total\_rows DO 
	SET @param\_employee\_number = (SELECT employee\_number FROM earned\_leaves LIMIT i,1);
	
	PREPARE query\_statement FROM 'CALL sp\_populate\_leave\_summary(?)';
	EXECUTE query\_statement USING @param\_employee\_number;
	
	-- UPDATE earned\_leaves SET earned\_leave = returned\_by\_EXECUTE
	SET i = i + 1;
END WHILE;

I want to save the value returned by the EXECUTE into a variable in order to use it in the next UPDATE statement. How can I do this please? Thanks, Jassim[^]

Technology News @ www.JassimRahma.com

Wendelius

Taken this is MySql, you could modify the stored procedure to have an OUT parameter. In such case you could then use two variables in your execute. So something like:

PREPARE query_statement FROM 'CALL sp_populate_leave_summary(?,?)';
EXECUTE query_statement USING @param_employee_number, @another_parameter;

jschell

Just noting that the code posted suggests that a loop should not be used, thus the question that derives from it is moot. SQL (as most dbs implement it) will do queries and updates on sets. The loop is implicit in it. It is just a matter of structuring it.

Jassim Rahma

I am getting:

Procedure execution failed
1048 - Column 'earned_leave' cannot be null

although the sp_populate_leave_summary returns the correct value as I can see in the result window. this is the code after adding an OUT:

WHILE i < total\_rows DO 
	SET @param\_employee\_number = (SELECT employee\_number FROM earned\_leaves LIMIT i,1);
	
	PREPARE query\_statement FROM 'CALL sp\_populate\_leave\_summary(?,?)';
	EXECUTE query\_statement USING @param\_employee\_number, @param\_eligible\_days;
	
	UPDATE earned\_leaves SET earned\_leave = @param\_eligible\_days WHERE employee\_number = @param\_employee\_number;
	SET i = i + 1;
END WHILE;

my OUT is:

OUT param_eligible_days int

Technology News @ www.JassimRahma.com

Wendelius

As the error says, the return value from your procedure to variable @param_eligible_days is null. Because of this null is being updated to the table and this is prohibited because the column is not null. So check why sp_populate_leave_summary returns NULL for the second parameter.