Executing Multiple SQL Statements in a Stored Procedure

Prerequisite:
How to create a stored procedure | Snowflake Stored Procedures | Snowflake
Pass argument to the Snowflake procedure
Tactically return multiple values from Stored Procedure in Snowflake

Code:
-----------
DROP DATABASE IF EXISTS RAMU;

CREATE DATABASE IF NOT EXISTS RAMU;

USE RAMU;

Create or replace table Stadium (id int, visit_date DATE NULL, people int);
insert into Stadium (id, visit_date, people) values ('1', '2017-01-01', '10');
insert into Stadium (id, visit_date, people) values ('2', '2017-01-02', '109');
insert into Stadium (id, visit_date, people) values ('3', '2017-01-03', '150');
insert into Stadium (id, visit_date, people) values ('4', '2017-01-04', '99');
insert into Stadium (id, visit_date, people) values ('5', '2017-01-05', '145');
insert into Stadium (id, visit_date, people) values ('6', '2017-01-06', '1455');
insert into Stadium (id, visit_date, people) values ('7', '2017-01-07', '199');
insert into Stadium (id, visit_date, people) values ('8', '2017-01-09', '188');

select * from Stadium;

create or replace table Sta_Employee
( id int,
name varchar(200),
salary int,
departmentId int);

insert into Sta_Employee
values
(1, 'Joe', 85000, 1),
(2, 'Henry', 80000, 2),
(3, 'Sam', 60000, 2),
(4, 'Max', 90000, 1),
(5, 'Janet', 69000, 1),
(6, 'Randy', 85000, 1),
(7, 'Will', 70000, 1);

select * from Sta_Employee;

create or replace table Employee2
( id int,
name varchar(200),
salary int,
departmentId int);

insert into Employee2
values
(8, 'Rajith', 85000, 1),
(9, 'Rituraj', 70000, 1);

select * from Employee2;

select * from "RAMU"."INFORMATION_SCHEMA"."TABLES" where TABLE_NAME like '%STA%' AND TABLE_SCHEMA='PUBLIC';

select TABLE_SCHEMA || '.' ||TABLE_NAME from "RAMU"."INFORMATION_SCHEMA"."TABLES" where TABLE_NAME like '%STA%' AND TABLE_SCHEMA='PUBLIC';

SELECT 'DROP TABLE ' || TABLE_SCHEMA || '.' || TABLE_NAME from "RAMU"."INFORMATION_SCHEMA"."TABLES" where TABLE_NAME like '%STA%' AND TABLE_SCHEMA='PUBLIC';

SELECT 'DROP TABLE ' || TABLE_SCHEMA || '.' || TABLE_NAME as execute_query from "RAMU"."INFORMATION_SCHEMA"."TABLES" where TABLE_NAME like '%STA%' AND TABLE_SCHEMA='PUBLIC';

create or replace procedure drop_table(Realtimedemo)
returns String not null
language javascript
as
$$
var my_sql_command = "SELECT 'DROP TABLE ' || TABLE_SCHEMA || '.' || TABLE_NAME as execute_query from RAMU.INFORMATION_SCHEMA.TABLES where TABLE_NAME like '%STA%' AND TABLE_SCHEMA='PUBLIC'";
var capture_result=" Executed the queries : "+"\n"
}
return capture_result; // Replace with something more useful.
$$
;

call drop_table();

Check this playlist for more AWS Projects in Big Data domain:

[vivektarab]

0:11 - Start

1:57 - Suppose business requirement is

5:39 - Now What we will do, we will create a Stored Procedure

8:26 - Now if we will see

9:49 - Now the Question comes

[Trendzzzat]

Thank you so much sir....best part i liked from ur videos is your providing everthing in description....thank you so much sir....very very helpful for me sir ...thank you sir

[AAhandles]

I wanted to know
in order to execute 3 stored procedures for which sql statements are stored in three SQL files. What do we have to choose either executing SQL process with for loop or for each loop container?

[tushargp]

Hi, can previous statements will be rolled back in case one in loop error out.

[keshavamugulursrinivasiyen]

Hi Team, i had a requirement to process the validation queries, i have rule_id, rule_desc, sql, expected result in a table, i need to execute the sql (stored in the table) against rule id and capture the output of the query and store it on a different table. Can you pls help me here.

[Anil_Rishvik_Memories]

Nice need info on logic implementation in sps with business use cases