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
-
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 @n8isjack0
Answers
-
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 @n8isjack0 -
Thank you. This helped me get to the next step in solving the problem ?
0 -
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 END0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive