Stored Procedure Archives

Snowflake Scripting Cursor Syntax and Examples

In my other article, we have discussed Snowflake scripting control structures and loops to manipulate Snowflake data. In this article, let us discuss how to define and use the Snowflake scripting cursor within a stored procedure and anonymous block. Snowflake also support JavaScript to write stored procedures, but it does not support cursor when JavaScript used. You can use the JavaScript result as a cursor alternative. Page Content Introduction Understanding Cursors in Snowflake How use Cursors in Snowflake Scripting? Declaring Cursors Opening Cursors Fetching Data from Cursors Closing Cursors Snowflake…

2 Comments

October 4, 2022

Redshift

Redshift SELECT INTO Variable – Example

The relational database that supports procedural language allows you to assign a value to local variable using SELECT statement within stored procedures. The databases such as Teradata, Oracle supports SELECT INTO clause to assign a value to a local variable. In this article, we will check how to assign a subquery value to a local variable using Redshift SELECT INTO clause within Stored Procedures. Redshift SELECT INTO Variable The SELECT INTO statement in Redshift retrieves data from one or more database tables, and assigns the selected values to variables. You can use SELECT…

Comments Off

December 13, 2021

Snowflake

Snowflake Error handling – Procedure and Functions

Snowflake Cloud data warehouse supports stored procedures to support migration from other relational databases. Snowflake allows you to write stored procedure using JavaScript APIs. It also supports error handling using try/catch block. However, it also supports built-in functions such as try_cast to handle error during type conversion. In this article, we will check methods for handling error in Snowflake procedures and functions. Snowflake Error handling The Snowflake allows you to handle errors with the help of try/catch block when you write a procedure or function using the JavaScript API. Snowflake…

2 Comments

July 23, 2021

Snowflake

How to Print SQL Query in Snowflake Stored Procedure?

As explained in my other article, to support migration from other relational databases, Snowflake supports the stored procedures. Snowflake uses JavaScript as a procedural language. It provides many features including control structures - branching, looping, Dynamic SQL, error handling, etc. But, JavaScript API, does not provide any print statement support to display the content of variable or SQL query itself. In this article, we will check how to print SQL query in Snowflake stored procedure. Print SQL Query in Snowflake Stored Procedure Snowflake stored procedure support many useful JavaScript API's.…

Comments Off

June 22, 2021

Redshift

Redshift Stored Procedure Array Variable Alternative

The ARRAY data type is a composite data value that consists of zero or more elements of a specified specific data type. Most of the relational databases such as Netezza, Teradata, etc. supports array variables to be defined inside stored procedures. As of now, Amazon Redshift does not support array variables. In this article, we will check, Redshift Stored Procedure array variable alternative. Redshift Stored Procedure Array Variable Support As mentioned, Amazon Redshift does not support array variables. In my other article, Amazon Redshift Array Support and Alternatives, we have…

Comments Off

July 21, 2020

Snowflake

Handle Cursor in Snowflake Stored Procedures – Examples

Snowflake stored procedures are used to encapsulate the data migration, data validation and business specific logic's. Stored procedure also handles an exceptions if any in your data or custom exception handling. The relational databases such as Oracle, Redshift, Netezza, etc. supports cursor variables. In this article, we will check how to handle cursor variable in Snowflake stored procedures with an example. Handle Cursor in Snowflake Stored Procedures In a relational database, cursors are extensively used in stored procedures to loop through the records from SELECT statements. Stored procedures encapsulate the business logic. For…

Comments Off

May 2, 2020

Snowflake

Snowflake Control Structures – IF, DO, WHILE, FOR

The best part about Snowflake is it supports JavaScript as a programming language to write stored procedures and user defined functions. The Stored procedure uses JavaScript to combine SQL with control structures such as branching and looping. In this article, we will check Snowflake branching and looping control structures. Snowflake Control Structures You can use two types of control structures inside stored procedures and user defined functions. Following are the Snowflake control structures Branching Structures - Sometimes called conditional control structures Looping Structures - Sometimes called Iterative control structures. Branching…

Comments Off

January 19, 2020

Snowflake

Snowflake Dynamic SQL Queries and Examples

When you migrate the data from other relational databases such as Netezza, Oracle, Redshift, etc, you will find the many queries which are generated on the fly and executed. These kind of queries are called dynamic queries. In the data warehouse, there may be a situation where you want to generate dynamic SQL queries based on your requirement. In this article, we will check how to generate and execute dynamic SQL queries in Snowflake with an example. Snowflake Dynamic SQL Queries Snowflake supports generating and executing dynamic queries in stored…

Comments Off

January 17, 2020

Snowflake

Snowflake Stored Procedures, Syntax, Limitations and Examples

To support migration from other relational databases, Snowflake supports the stored procedures. In this article, we will check Snowflake stored procedures, syntax, usage and restrictions with some examples. Stored procedure combines the complex SQL business logic with procedural statements. Snowflake Stored Procedures Overview Snowflake stored procedures are used to encapsulate the data migration, data validation and business specific logic's and same time handle the exceptions if any in your data or custom exception handling. For example, suppose that you want to clean up a database by deleting data older than a…

Comments Off

January 17, 2020

Snowflake

Difference Between Snowflake Stored Procedure and UDFs – SP vs UDFs

You can use both stored procedure and user defined functions to encapsulate the business logic and achieve specific tasks. However, there are important differences between UDFs and stored procedures. In this article, we will check the difference between Snowflake stored Procedure and user defined functions (UDFs). Difference Between Snowflake Stored Procedure and UDFs The Snowflake stored procedure vs user defined function is an interesting comparison. Both are used to achieve certain functionalities that are not possible using built-in functions available in Snowflake. Following are some import comparison between Snowflake stored…

Comments Off

January 14, 2020