SQL stored procedure does not work

Reply
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
Orange 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
Orange 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

Highlighted
Visitor

Re: SQL stored procedure does not work

My stored procedure is not working, how should I break this up?

Declare @SearchStr nvarchar(100)

SET  @SearchStr='Search String' BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128),
 @SearchStr2 nvarchar(110)  SET  @TableName = ''    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL    
BEGIN       
  SET @ColumnName = ''      
  SET @TableName =  (
    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' +    QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES 
    WHERE    TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(      OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
        'IsMSShipped') = 0)

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)      
  BEGIN
    SET @ColumnName = (
      SELECT MIN(QUOTENAME(COLUMN_NAME))
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
        AND TABLE_NAME = PARSENAME(@TableName, 1)
      AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
      AND QUOTENAME(COLUMN_NAME) > @ColumnName)
      IF @ColumnName IS NOT NULL            
      BEGIN
      INSERT INTO #Results
      EXEC
      (
        'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + 
          ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
        ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
      )             
      END       
    END     
  END

  SELECT ColumnName, ColumnValue FROM #Results END
Announcements
Call for Presenters! We want to hear your Domo story, submit your entry and win a free pass to Domopalooza 2019!. Click here!