SQL stored procedure does not work

ChatterBox
ChatterBox Member
edited March 2023 in Datasets

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 

Best Answer

  • n8isjack-ret
    n8isjack-ret Domo Employee
    Answer ✓

     

    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).

     

     

     

    Former Domo employee you can find me in the Dojo Community here @n8isjack

Answers

  • n8isjack-ret
    n8isjack-ret Domo Employee
    Answer ✓

     

    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).

     

     

     

    Former Domo employee you can find me in the Dojo Community here @n8isjack
  • ChatterBox
    ChatterBox Member

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

  • user01011
    user01011 Member

    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 = PARSENAMETableName, 2)
    AND TABLE_NAME = PARSENAMETableName, 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

Categories