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
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
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
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.
