CREATE PROCEDURE

Translation reference to convert SQL Server CREATE PROCEDURE clauses to Snowflake

Some parts in the output code are omitted for clarity reasons.

Description

The create procedure statement allows the creation of stored procedures that can:

Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
Contain programming statements that perform operations in the database, including calling other procedures.
Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

For more information regarding SQL Server CREATE PROCEDURE, check here.

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

Sample Source Patterns

Stored procedure without body

A stored procedure without a body is an unusual scenario that is allowed in SQL Server. Snowflake Scripting does not allow to define procedures without a body, but the following example shows the equivalence.

SQL Server

IN -> SqlServer_01.sql

CREATE PROC SampleProcedure AS;

Snowflake Scripting

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE SampleProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      RETURN '';
   END;
$$;

Basic stored procedure

The following example details a simple stored procedure that will include a new Privacy department into the AdventureWorks2019 database.

SQL Server

IN -> SqlServer_02.sql

CREATE OR ALTER PROCEDURE Add_Privacy_Department
AS
  EXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');

Snowflake Scripting

OUT -> SqlServer_02.sql

CREATE OR REPLACE PROCEDURE Add_Privacy_Department ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE 'INSERT INTO HumanResources.Department VALUES ('Privacy', 'Executive General and Administration', default);';
  END;
$$;

Alter procedure

The transformation for the ALTER procedure is equivalent to the basic procedure.

SQL Server

IN -> SqlServer_03.sql

ALTER PROCEDURE procedureName
AS
SELECT 1 AS ThisDB;

Snowflake Scripting

OUT -> SqlServer_03.sql

CREATE OR REPLACE PROCEDURE procedureName ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
ProcedureResultSet RESULTSET;
BEGIN
ProcedureResultSet := (
SELECT 1 AS ThisDB);
RETURN TABLE(ProcedureResultSet);
END;
$$;

Using parameters

You can use parameters to drive your logic or construct dynamic SQL statements inside your stored procedure. In the following example a simple SetNewPrice stored procedure is constructed, which sets a new product price based on the arguments sent by the caller.

SQL Server

IN -> SqlServer_04.sql

CREATE OR ALTER PROCEDURE SetNewPrice @ProductID INT, @NewPrice MONEY
AS
  BEGIN
    DECLARE @dynSqlStatement AS VARCHAR(300);
    SET @dynSqlStatement = 'UPDATE Production.ProductListPriceHistory SET ListPrice = ' + CAST(@NewPrice AS VARCHAR(10)) + ' WHERE ProductID = ' + CAST(@ProductID AS VARCHAR(10)) + ' AND EndDate IS NULL';
    EXECUTE (@dynSqlStatement);
  END;

Snowflake Scripting

OUT -> SqlServer_04.sql

CREATE OR REPLACE PROCEDURE SetNewPrice (PRODUCTID INT, NEWPRICE NUMBER(38, 4))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    DYNSQLSTATEMENT VARCHAR(300);
  BEGIN
     
    DYNSQLSTATEMENT := 'UPDATE Production.ProductListPriceHistory
   SET
      ListPrice = ' || CAST(:NEWPRICE AS VARCHAR(10)) || '
   WHERE
      ProductID = ' || CAST(:PRODUCTID AS VARCHAR(10)) || '
      AND EndDate IS NULL;';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE :DYNSQLSTATEMENT;
  END;
$$;

Output Parameters

SQL Server output keyword indicates that the parameter is an output parameter, which value will be returned to the stored procedure caller. For example, the following procedure will return the number of vacation hours of a specific employee.