Skip to main content

Stored Procedures allow us to write functions inside our SQL code. In Snowflake you can use Scala in addition to usable UDF languages. The main difference between Stored Procedures and UDFs is that Store Procedure could not return a value; means that it can but that isn’t mandatory. (If you want to look more about UDF’s you can check this link).

As it’s with UDFs you have ability to write these procedures and call it in any part of your code. Both of them helps us to simplfy our code complexity.

The biggest feature of Stored Procedures is that their ability to make DML functions inside of them. You can execute SELECT, UPDATE, INSERT.. to make several changes on your tables. This is the biggest case of use of Stored Procedures that we couldn’t do it with UDFs.

Let’s create an example Stored Procedure that takes a value as a input, and checks if that value is positive. If it is positive it will insert that value in our table positive number; if it is negative it will intert it in the table negative number and if it is zero it will return “You entered 0 as a value.”

Stored Procedures Example

First let’s create our tables:

CREATE TABLE positive_number(value integer);
CREATE TABLE negative_number(value integer);

now we have our tables, we can start to write our stored procedure

CREATE OR REPLACE procedure send_integer(input integer)
    RETURNS varchar
    LANGUAGE sql
    AS
    $$
    declare
      error varchar default 'You entered 0, number cant go inside a table.';
    
    begin
      
      if (input = 0) then
        return error;
      end if;
    
      if (input > 0) then
          INSERT INTO positive_number VALUES (:input);
      end if;
    
      if (input < 0) then
          INSERT INTO negative_number VALUES (:input);
      end if;
    end;
    $$
    ;

Note: To use the given value in function in our DML statement we should use ‘:‘ sign. It will insert the given value in to the table that we wanted.

CALL send_integer(0);    --we expect to have a text return and no insert into our tables
result of function call with value = 0
result of function call with value = 0

as we expect we don’t have any values inside of our tables and has the text output. Now let’s try to call our function with 1 and see if our positive_number table will change:


CALL send_integer(1); --we expect no text return and insert 1 into our positive_number table
result of function call with value = 1
result of function call with value = 1

as we can see now we have 1 new value in our positive_number table and no returns from the function. For last let’s try the call same function with -1:


CALL send_integer(-1); --we expect no text return and insert -1 into our negative_number table
result of function call with value = -1
result of function call with value = -1

as we expected, function has inserted -1 into the negative_number table and didn’t returned any text.


Conclusion

A lost note is that besides the language we used in our example, we could still use other languages that allows us Stored Procedure.

We can use Stored Procedures while we have to do DML statements, such as SELECT, INSERT, UPDATE… or DDL operations . The reminder that this is their biggest difference between UDFs and Store Procedures; while they can still return a value but their not enforced to.

If you want to have additional information about UDFs you can check this blog.

Credits

https://docs.snowflake.com

Auteur

  • Can Hobek

    Hello, my name is Can Höbek. I am originally from Turkey, and I currently live in Milan. In my second year of high school, I did a year of exchange in Turin to prepare myself for university and improve my Italian language. I am currently in my final year of bachelor degree in Politecnico di Torino in Management Engineering. During my time there, I was involved in various extracurricular activities, such as volunteer organizations that make science laboratories for primary school students with specific topics that point towards the 2030 goals , which helped me to develop my ability to work in a team and developed my soft-skills. From primary school up to last year of high school, for 10 years I went to conservatory school specializing in classic guitar. I always loved to play different instruments even though my professionality is on classic guitar. Another part that creates me is sports; I love all types of sports but I played professionally in some teams as a football, volleyball player. Before my last year of university I had started to think about my interests and did some research on how to improve myself on that. I found myself wanting to improve on the data and programming part that I started to learn in my first years of university. When I was doing these researches, I found Nimbus that offers first a formation on specific topics that was my area of interest and then gives us an opportunity to work on some projects that you have had formation. The most important part for me was to feel myself learning new things in my area of interest and then working on it.

Can Hobek

Hello, my name is Can Höbek. I am originally from Turkey, and I currently live in Milan. In my second year of high school, I did a year of exchange in Turin to prepare myself for university and improve my Italian language. I am currently in my final year of bachelor degree in Politecnico di Torino in Management Engineering. During my time there, I was involved in various extracurricular activities, such as volunteer organizations that make science laboratories for primary school students with specific topics that point towards the 2030 goals , which helped me to develop my ability to work in a team and developed my soft-skills. From primary school up to last year of high school, for 10 years I went to conservatory school specializing in classic guitar. I always loved to play different instruments even though my professionality is on classic guitar. Another part that creates me is sports; I love all types of sports but I played professionally in some teams as a football, volleyball player. Before my last year of university I had started to think about my interests and did some research on how to improve myself on that. I found myself wanting to improve on the data and programming part that I started to learn in my first years of university. When I was doing these researches, I found Nimbus that offers first a formation on specific topics that was my area of interest and then gives us an opportunity to work on some projects that you have had formation. The most important part for me was to feel myself learning new things in my area of interest and then working on it.