Stored Procedures

 examples of Stored procedures

Example :

CREATE OR REPLACE PROCEDURE sp_concatenate_strings(
    first VARCHAR,
    second VARCHAR,
    third VARCHAR)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  $$
  BEGIN
    RETURN first || second || third;
  END;
  $$;

call sp_concatenate_strings(

first => 'REddy',

second => "muralidhar",

pujari => "Pujari)

);

/************************************************/

create or replace procedure get_row_count(table_name VARCHAR)
  returns float not null
  language javascript
  as
  $$
  var row_count = 0;
  // Dynamically compose the SQL statement to execute.
  var sql_command = "select count(*) from " + TABLE_NAME;
  // Run the statement.
  var stmt = snowflake.createStatement(
         {
         sqlText: sql_command
         }
      );
  var res = stmt.execute();
  // Get back the row count. Specifically, ...
  // ... get the first (and in this case only) row from the result set ...
  res.next();
  // ... and then get the returned value, which in this case is the number of
  // rows in the table.
  row_count = res.getColumnValue(1);
  return row_count;
  $$
  ;

  call get_row_count('TRAINING.DEMO.EMP');

/*****************************************************************/

CREATE OR REPLACE PROCEDURE validate_age (age float)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS 
$$
    try {
        if (AGE < 0) {
            throw "Age cannot be negative!";
        } else {
            return "Age validated.";
        }
    } catch (err) {
        return "Error: " + err;
    }
$$;

call validate_age(20);

/**********************************************************/

CREATE OR REPLACE TABLE CUSTOMERS
(
cust_id integer,
name varchar(100),
location varchar(100),
UAN number,
item_ordered varchar(100),
status varchar(20)
);

Insert into customers
values (100,'Sam','London',78654,'Thermo-Heater','active'),
(101,'Smith','Germany',12654,'chrome speaker','active'),
(102,'Ram','London',78765,'Jabra headset','in-active'),
(103,'Abhishek','India',71234,'croma pro','active'),
(104,'Praful','France',70984,'air vent','in-active');

select * from CUSTOMERS;

CREATE OR REPLACE PROCEDURE purge_data_by_status(in_status VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
AS
 $$
    DECLARE
        message VARCHAR;
    BEGIN
        DELETE FROM customers WHERE status = :in_status;
        message := in_status ||' customers data deleted sucessfully';
        RETURN message;
    END;
 $$
;

call purge_data_by_status('in-active');

/**************************************************************************/

CREATE OR REPLACE PROCEDURE process_emp_data()

RETURNS STRING

LANGUAGE JAVASCRIPT

AS

$$

    var result = "";

    var emp_id;

    var emp_name;

    

    var sqlStatement = `

        SELECT emp_id, name

        FROM emp;

    `;

    

    // Declare the cursor

    var cursor = snowflake.execute({ sqlText: sqlStatement });

    

    // Loop through the result set

    while (cursor.next()) {

        emp_id = cursor.getColumnValue(1);

        emp_name = cursor.getColumnValue(2);

        

        // Perform some processing with the data

        result += "Processing employee: " + emp_id + " - " + emp_name + "\n";

    }

    

    //cursor.close();

    

    return result;

$$;

CALL process_emp_data();