CODEWITHSUNDEEP
mysql
Atwp67
Atwp67

Reputation: 307

Stored procedure parameter to variables

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.

Upvotes: 0

Views: 84

Answers (1)

Mehran
Mehran

Reputation: 16831

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');

Upvotes: 1

Related Questions