Writing Stored Procedures in Snowflake Scripting

This topic explains how to write a stored procedure in SQL by using Snowflake Scripting.

In this Topic:

Introduction

To write a stored procedure that uses Snowflake Scripting:

Note the following:

The following is an example of a simple stored procedure that returns the value of the argument that is passed in:

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  RETURN message;
END;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  RETURN message;
END;
$$
;

The following is an example of calling the stored procedure:

CALL output_message('Hello World');

Using Arguments Passed to a Stored Procedure

If you pass in any arguments to your stored procedure, you can refer to those arguments by name in any Snowflake Scripting expression. See the next sections for more details:

Simple Example of Using Arguments Passed to a Stored Procedure

The following stored procedure uses the values of the arguments in IF and RETURN statements.

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;
$$
;

The following is an example of calling the stored procedure:

CALL return_greater(2, 3);

Using an Argument in a SQL Statement (Binding)

As is the case with Snowflake Scripting variables, if you need to use an argument in a SQL statement, put a colon (:) in front of the argument name. (See Using a Variable in a SQL Statement (Binding).)

For example, the following stored procedure uses the id argument in the WHERE clause of a SELECT statement. In the WHERE clause, the argument is specified as :id.

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;
$$
;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

The following is an example of calling the stored procedure:

CALL find_invoice_by_id('2');

Using an Argument as an Object Identifier

If you need to use an argument to refer to an object (e.g. a table name in the FROM clause of a SELECT statement), use the IDENTIFIER keyword to indicate that the argument represents an object identifier. For example:

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;
$$
;

The following is an example of calling the stored procedure:

CALL get_row_count('invoices');

Using an Argument When Building a String for a SQL Statement

Note that if you are building a SQL statement as a string to be passed to EXECUTE IMMEDIATE (see Assigning a Query to a Declared RESULTSET), do not prefix the argument with a colon. For example:

CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  select_statement VARCHAR;
  res RESULTSET;
BEGIN
  select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Returning Tabular Data

If you need to return tabular data (e.g. data from a RESULTSET) from your stored procedure, specify RETURNS TABLE(...) in your CREATE PROCEDURE statement.

If you know the Snowflake data types of the columns in the returned table, specify the column names and types in the RETURNS TABLE().

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
...

Otherwise (e.g. if you are determining the column types during run time), you can omit the column names and types:

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE ()
...

Note

Currently, in the RETURNS TABLE(...) clause in CREATE PROCEDURE, you cannot specify GEOGRAPHY as a column type.

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...

If you do so, calling the stored procedure results in the error:

CALL test_return_geography_table_1();
Stored procedure execution error: data type of returned table does not match expected returned table type

To work around this, you can omit the column arguments and types in RETURNS TABLE().

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...

If you need to return the data in a RESULTSET, use TABLE() in your RETURN statement.

For example:

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
  RETURN TABLE(res);
END;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
  RETURN TABLE(res);
END;
$$
;

The following is an example of calling the stored procedure:

CALL get_top_sales();

Calling a Stored Procedure From Another Stored Procedure

In a stored procedure, if you need to call another stored procedure, use one of the following approaches:

Calling a Stored Procedure Without Using the Returned Value

Use a CALL statement to call the stored procedure (as you normally would).

If you need to pass in any variables or arguments as input arguments in the CALL statement, remember to use a column (:) in front of the variable name. (See Using a Variable in a SQL Statement (Binding).)

The following is an example of a stored procedure that calls another stored procedure but does not depend on the return value.

First, create a table for use in the example:

-- Create a table for use in the example.
CREATE OR REPLACE TABLE int_table (value INTEGER);

Then, create the stored procedure that you will call from another stored procedure:

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
$$
;

Next, create a second stored procedure that calls the first stored procedure:

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;
$$
;

Finally, call the second stored procedure:

CALL insert_two_values(4, 5);

Using the Value Returned From a Stored Procedure Call

If you need to use the return value of the stored procedure that you are calling, set a RESULTSET to a string containing the CALL statement. (See Assigning a Query to a Declared RESULTSET.)

To retrieve the return value from the call, you can use a CURSOR for the RESULTSET. Note that when accessing the result of the call, the name of the column containing the result is the name of the stored procedure that you called.

For example, if you called the stored procedure get_row_count, you can get the value from the row_variable.get_row_count.

The following example calls the get_row_count stored procedure that was defined in Using an Argument as an Object Identifier.

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
DECLARE
  res1 RESULTSET;
BEGIN
  res1 := (CALL get_row_count(:table_name));
  LET c1 CURSOR FOR res1;
  FOR row_variable in c1 DO
    IF (row_variable.get_row_count > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
 END FOR;
END;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  res1 RESULTSET;
BEGIN
  res1 := (CALL get_row_count(:table_name));
  LET c1 CURSOR FOR res1;
  FOR row_variable in c1 DO
    IF (row_variable.get_row_count > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
 END FOR;
END;
$$
;

The following is an example of calling the stored procedure:

CALL count_greater_than('invoices', 3);
Back to top