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 

Best Answer

  • n8isjack-ret
    Accepted 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).

     

     

     

Answers

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

  • 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