SQL stored procedure does not work

Reply
Highlighted
White Belt

SQL stored procedure does not work

Hi,

I've tried creating a procedure to spread revenue over 13 periods using MySQL as follows. Error's thrown at declaration/ setting variables. 

 

drop procedure if exists load_foo_test_data;

delimiter $$
create procedure load_foo_test_data()
BEGIN

Set @v_max = 13;
Set @v_counter =1;

start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end; $$

delimiter ;

call load_foo_test_data();

 

Please advice me on this .. thanks 


Accepted Solutions
Yellow Belt

Re: SQL stored procedure does not work

 

In Domo each transform can only run a single command. You have, by my count, 5 commands trying to run and it will throw an error.

 

So here is your script, but I've marked each line that Domo will consider to be an individual command:


* drop procedure if exists load_foo_test_data;
* delimiter $$
* create procedure load_foo_test_data()
BEGIN
Set @v_max = 13;
Set @v_counter =1;
start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end; $$
* delimiter ;
* call load_foo_test_data();


I think you'll need to do three seperate transforms in Domo.

 

Transform 1:
DROP PROCEDURE IF EXISTS load_foo_test_data;

 

Transform 2:
CREATE PROCEDURE load_foo_test_data()
BEGIN
Set @v_max = 13;
Set @v_counter =1;
start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end;

 

Transform 3:
CALL load_foo_test_data();

 

Note: Once you've run the second transform, you cannot run it again unless you run the first one (to drop the procedure).

 

 

 


All Replies
Yellow Belt

Re: SQL stored procedure does not work

 

In Domo each transform can only run a single command. You have, by my count, 5 commands trying to run and it will throw an error.

 

So here is your script, but I've marked each line that Domo will consider to be an individual command:


* drop procedure if exists load_foo_test_data;
* delimiter $$
* create procedure load_foo_test_data()
BEGIN
Set @v_max = 13;
Set @v_counter =1;
start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end; $$
* delimiter ;
* call load_foo_test_data();


I think you'll need to do three seperate transforms in Domo.

 

Transform 1:
DROP PROCEDURE IF EXISTS load_foo_test_data;

 

Transform 2:
CREATE PROCEDURE load_foo_test_data()
BEGIN
Set @v_max = 13;
Set @v_counter =1;
start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end;

 

Transform 3:
CALL load_foo_test_data();

 

Note: Once you've run the second transform, you cannot run it again unless you run the first one (to drop the procedure).

 

 

 

White Belt

Re: SQL stored procedure does not work

Thank you. This helped me get to the next step in solving the problem Smiley Happy

Announcements
Domopalooza 2018! Pre-conference training registration is now open! Click here!