CODEWITHSUNDEEP
mysqlmysql-workbench
priyanka.sarkar
priyanka.sarkar

Reputation: 26498

How to properly invoke MySQL stored procedure with out parameter?

I have a MySQL SP as under

DELIMITER //

CREATE PROCEDURE prc_testProc(
    IN p_input varchar(32),    
    out v_output BIGINT)
BEGIN 

    SET v_output = 1000; -- default sequence value

    IF (p_input = 'test1') THEN

        SET v_output = (Select MAX(a.id) FROM tbl1 a WHERE a.colunmname='somename');

    ELSEIF (p_input = 'test2') THEN

        SET v_output = (Select MAX(a.id) FROM tbl2 a WHERE a.colunmname='somename');    

    ELSE 
        SET v_output = 1000;

    END IF;
END;
//

DELIMITER ;

It compiles fine. But when I am calling it as under

call prc_testProc('ww',@v_output);

I get only "0 rows affected"

What am I doing wrong? I am expecting the default value i.e. 1000 in this case.

Upvotes: 0

Views: 54

Answers (2)

slaakso
slaakso

Reputation: 9050

Pass a variable as a second parameter. If you call it from MySQL Workbench, use user defined variable (@var), if you call it from another procedure it can be a normal variable (DECLARE).

call prc_testProc('ww',@v_output);

select @v_output;

Upvotes: 1

Barmar
Barmar

Reputation: 780843

The variable will be set to the value assigned in the procedure. You can then use that variable in another query.

CALL pr_testProc('ww', @testproc_result);
SELECT @testproc_result;

Upvotes: 1

Related Questions