Articles in this section

Calling SQL stored procedures

Anitha Abraham
Updated

Stored procedures perform database operations based on SQL statements. Beyond traditional queries, stored procedures can also handle complex transactions or loads. The Celigo platform can work as an orchestration tool to precisely execute stored procedures in the optimal time.

SQL stored procedures in ELT flows

Using database connections in the Celigo platform, you can seamlessly leverage the potential of SQL stored procedures in ELT (Extract, Load, Transform) flows.

Example: Consider a scenario in a NetSuite-Snowflake integration with many flows where you want to:

  • first, extract and load the data from NetSuite to Snowflake tables
  • then, transform the data to create a data warehouse in Snowflake

Since the integration has many flows, create an execution plan to run the flows. That is, schedule a run of the first flow, then daisy chain the rest of the flows. The initial flows will extract and load the data, and the last flow will call a stored procedure to transform the data as required for the data warehouse.

Before you call a procedure, write your stored procedure in the database using a language such as JavaScript. The generic format for a stored procedure is shown below. It's good to keep your stored procedures modular so that you can reuse them. For more details, see your database documentation.

```sql
  CREATE OR REPLACE PROCEDURE transform_data()
  RETURNS STRING
  LANGUAGE JAVASCRIPT
  EXECUTE AS CALLER
  AS
  $$
  // Your transformation logic here
  return 'Transformation completed successfully';
  $$;
  ```

Execute the SQL CALL statement for stored procedures

In the example scenario, when all the flows to extract data from NetSuite and load it into Snowflake tables have been completed, the flow to transform data is run. This last flow will export the loaded data from Snowflake and execute the stored procedure to transform data using the CALL statement that you provide in the SQL Query setting of your export.

configure-sql-query.png Add the SQL query to call a stored procedure

You won't see the transformed records in the export Preview; but, you can preview any message that is returned and check whether the SQL CALL statement is successfully executed. After the flow is run, you can view the transformed records in Snowflake.

Similarly, you can consider calling stored procedures in your flows to optimize performance and efficiency.