MySQL INSERT with CALL

Question

I have a stored procedure that splits a string and ends with a select.

I would like to run an insert on the stored procedure like you would do an insert on a select

Something like this

INSERT INTO ....
CALL sp_split...

My split looks like this:

    DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `split_with_id`(id INT, input varchar(1000), delim VARCHAR(10))
BEGIN
    declare foundPos tinyint unsigned; 
    declare tmpTxt varchar(1000); 
    declare delimLen tinyint unsigned; 
    declare element varchar(1000); 

drop temporary table if exists tmpValues; 
create temporary table tmpValues 
( 
    `id` int not null default 0,
    `values` varchar(1000) not null default '' 
) engine = memory; 

set delimLen = length(delim); 
set tmpTxt = input; 

set foundPos = instr(tmpTxt,delim); 

while foundPos <> 0 do 
    set element = substring(tmpTxt, 1, foundPos-1); 
    set tmpTxt = replace(tmpTxt, concat(element,delim), ''); 


    insert into tmpValues (`id`, `values`) values (id, element); 

    set foundPos = instr(tmpTxt,delim); 
end while; 

if tmpTxt <> '' then 
    insert into tmpValues (`id`, `values`) values (id, tmpTxt); 
end if; 

select * from tmpValues;

END

I believe this question may be what you are trying to achieve http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure — Robb, Feb 07 '12 at 16:06
That is a grate article for MS SQL but unfutunately MySQL does not support exec — esack, Feb 07 '12 at 20:37

score 1 · Answer 1 · answered Feb 07 '12 at 21:41

1

Create a wrapper function and have it call the procedure. Then SELECT it normally.

DELIMITER $$

CREATE FUNCTION `f_wrapper_split` (strin VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
    DECLARE r VARCHAR(255);
    CALL sp_split(strin);
    RETURN r;
END
$$

Of course, if sp_split returns multiple results, you'll need to adapt the function to, perhaps, take an INT input as well and return you that particular result. Then just call it multiple times.

It's not very pretty, but that's the best I can think of offhand.

answered Feb 07 '12 at 21:41

Naltharial

2,132
14
21

Would it be possible to include the DDL for `sp_split` into the question? – Naltharial Feb 07 '12 at 22:15
My split returns a table: AccountID | PermissionID I need to find a way to do the folloing code "INSERT INTO tablename (AccountID, PermissionID ) SELECT AccountID, PermissionID FROM tempTble" but with a "CALL" Something like "INSERT INTO tablename (AccountID, PermissionID ) CALL sp_split()" – esack Feb 07 '12 at 22:16
Are you doing a `SELECT` inside a procedure and letting it bleed the resultset to the outer scope? Because that's not the same as returning a `TABLE` type in SQL Server or the like. – Naltharial Feb 07 '12 at 22:25
I am not returning a table I am calling select at the end do you have a better salution I added my split in the question above – esack Feb 07 '12 at 22:36
Why don't you just insert into the `tmpValues` table? Why bother with the `SELECT` at all? – Naltharial Feb 07 '12 at 23:24

MySQL INSERT with CALL

1 Answers1