insert into table as data return from stored procedure

Question

i want to store data returned from stored procedure to a table. i have tried this.

insert into table1 call sp_test();

but it fails.

how do i do this ?

possible duplicate of [MySQL How to INSERT INTO \[temp table\] FROM \[Stored Procedure\]](http://stackoverflow.com/questions/687102/mysql-how-to-insert-into-temp-table-from-stored-procedure) — juergen d, Jun 28 '12 at 06:42

score 1 · Answer 1 · answered Jun 28 '12 at 06:42

1

Try this

call sp_test(@var)    
insert into table1 (select @var);

And in procedure there shoud be out variable in arguments

answered Jun 28 '12 at 06:42

Muhammad Raheel

This is the beginning of the right answer: you can't redirect or use the 'output' of the stored procedures. The answer would be better completed with how the inside of the stored procedure should look like. – snooze92 Dec 10 '13 at 18:10

score 0 · Answer 2 · answered Jun 28 '12 at 06:44

0

If it returns data, it is a stored function, not a stored procedure. In that case you don't use CALL but simply use it in a INSERT … SELECT statement, like this:

INSERT INTO table1 SELECT sp_test()

answered Jun 28 '12 at 06:44

MvG

Then it has to have parameters to return anything through them, and your example didn't indicate any. WIthout those, the result of your queries inside the SP will be lost once the procedure returns. – MvG Jun 28 '12 at 07:00

score -1 · Answer 3 · edited May 23 '17 at 12:24

-1

for function use select

insert into table1 select sp_test();

edited May 23 '17 at 12:24

Community

answered Jun 28 '12 at 06:47

1

my sp return more than one row while function return only one value – Chetan Patel Jun 28 '12 at 06:48

3 Answers3