CODEWITHSUNDEEP
mysqlstored-procedures
NITHIN
NITHIN

Reputation: 1

How to call a stored procedure with in and out parameters using mysql prepare and execute statement?

I know this code does not work!!! But need a code which will work in this approach.

SET @sp_name = "sp_user";
SET @response = "";
SET @sp_call = CONCAT('CALL ',@sp_name,'(',@request,',@response)');
PREPARE STMT from @sp_call;
EXECUTE STMT;
SELECT @response;

response is out parameter

Thanks

Upvotes: 0

Views: 205

Answers (1)

Akina
Akina

Reputation: 42622

An example:

CREATE PROCEDURE sp_user (IN arg1 TEXT, OUT arg2 TEXT)
BEGIN
    IF arg1 = 'abc' THEN
        SET arg2 = 'abc provided';
    ELSE
        SET arg2 = 'Something else provided';
    END IF;
END

SET @sp_name = 'sp_user';
SET @request = '\'abc\'';
SET @sp_call = CONCAT('CALL ',@sp_name,'(',@request,',@response)');
SELECT @sp_call;
PREPARE STMT from @sp_call;
EXECUTE STMT;
SELECT @response;

| @sp_call                      |
| :---------------------------- |
| CALL sp_user('abc',@response) |

| @response    |
| :----------- |
| abc provided |

SET @sp_name = 'sp_user';
SET @request = '\'def\'';
SET @sp_call = CONCAT('CALL ',@sp_name,'(',@request,',@response)');
SELECT @sp_call;
PREPARE STMT from @sp_call;
EXECUTE STMT;
SELECT @response;

| @sp_call                      |
| :---------------------------- |
| CALL sp_user('def',@response) |

| @response               |
| :---------------------- |
| Something else provided |

db<>fiddle here

Upvotes: 1

Related Questions