Stored procedure parameter to variables

Question

This is one of those "can it be done" questions. I've got a stored procedure that could take quite a few input parameters so I was wondering if I can establish one input parameter and then set variables based off the comma separated input. Example...

drop procedure if exists sptest;
delimiter $$
create procedure sptest (v1 varchar(254))
begin
if v1=1 then set @vx1:='test1';end if;
if v1=2 then set @vx2:='test2';end if;
if v1=3 then set @vx3:='test3';end if;
if v1=4 then set @vx4:='test4';end if;
select v1;
select @vx1,@vx2,@vx3,@vx4;
end
call sptest('1,2,3,4');

If possible, any examples/guidance would be appreciated.

You can parse the input String try use some like this: http://stackoverflow.com/questions/11835155/mysql-split-comma-seperated-string-into-temp-table — esdebon, Feb 28 '14 at 18:51

score 1 · Accepted Answer · answered Feb 28 '14 at 18:54

MySQL comes with a string function named FIND_IN_SET. I think that's your answer. Here's how it works for you:

DROP PROCEDURE IF EXISTS sptest;

DELIMITER $$
CREATE PROCEDURE sptest (v1 VARCHAR(254))
BEGIN
    IF FIND_IN_SET('1', v1) THEN SET @vx1:='test1'; END IF;
    IF FIND_IN_SET('2', v1) THEN SET @vx2:='test2'; END IF;
    IF FIND_IN_SET('3', v1) THEN SET @vx3:='test3'; END IF;
    IF FIND_IN_SET('4', v1) THEN SET @vx4:='test4'; END IF;
    SELECT v1;
    SELECT @vx1, @vx2, @vx3, @vx4;
END$$

CALL sptest('1,2,3,4');

You're welcome, just make sure you read the documentation on that. You need to know its limitations. — Mehran, Feb 28 '14 at 19:18

Stored procedure parameter to variables

1 Answers1