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

Procedure

IN -> SqlServer_01.sql

CREATE PROC SampleProcedure AS;

Snowflake Scripting

Procedure

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

Procedure

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

Procedure

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

Procedure

IN -> SqlServer_03.sql

ALTER PROCEDURE procedureName
AS
SELECT 1 AS ThisDB;

Snowflake Scripting

Procedure

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

Procedure

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

Procedure

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.

SQL Server

Procedure

IN -> SqlServer_05.sql

CREATE PROCEDURE GetVacationHours  
   @employeeId INT,  
   @vacationHours INT OUTPUT  
AS  
BEGIN  
   SELECT @vacationHours = VacationHours 
   FROM HumanResources.Employee
   WHERE NationalIDNumber = @employeeID
END;

Snowflake Scripting

Procedure

OUT -> SqlServer_05.sql

CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS INT)
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
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID);
      RETURN TABLE(ProcedureResultSet);
      !!!RESOLVE EWI!!! /*** SSC-EWI-TS0072 - RETURN statement will be ignored due to previous RETURN statement ***/!!!
      RETURN VACATIONHOURS;
   END;
$$;

Only 1 output parameter can be returned from a Snowflake scripting stored procedure.

Optional Parameters

A parameter is considered optional if the parameter has a default value specified when it is declared. It is not necessary to provide a value for an optional parameter in a procedure call.

SQL Server

Procedure

IN -> SqlServer_06.sql

CREATE PROCEDURE OPTIONAL_PARAMETER @VAR1 INT = 1, @VAR2 INT = 2
AS
    BEGIN
        RETURN NULL;
    END

GO

EXEC OPTIONAL_PARAMETER @VAR2 = 4

Snowflake Scripting

Procedure

OUT -> SqlServer_06.sql

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

CALL OPTIONAL_PARAMETER(VAR2 => 4);

EXECUTE AS

SQL Server's EXECUTE AS clause defines the execution context of the stored procedure, specifying which user account the Database Engine uses to validate permissions on objects that are referenced within the procedure. For example, we can modify the previous GetVacationHours procedure to define different execution contexts.

• Owner (default in Snowflake Scripting)

SQL Server

Procedure

IN -> SqlServer_07.sql

CREATE OR ALTER PROCEDURE GetVacationHours
   @employeeId INT,  
   @vacationHours INT OUTPUT
WITH EXECUTE AS OWNER
AS
BEGIN  
   SELECT @vacationHours = VacationHours 
   FROM HumanResources.Employee
   WHERE NationalIDNumber = @employeeID
END;

Snowflake Scripting

Procedure

OUT -> SqlServer_07.sql

CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS INT)
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS OWNER
AS
$$
   DECLARE
      ProcedureResultSet RESULTSET;
   BEGIN
      ProcedureResultSet := (
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID);
      RETURN TABLE(ProcedureResultSet);
      !!!RESOLVE EWI!!! /*** SSC-EWI-TS0072 - RETURN statement will be ignored due to previous RETURN statement ***/!!!
      RETURN VACATIONHOURS;
   END;
$$;

• Caller

SQL Server

Procedure

IN -> SqlServer_08.sql

CREATE OR ALTER PROCEDURE GetVacationHours
   @employeeId INT,  
   @vacationHours INT OUTPUT
WITH EXECUTE AS CALLER
AS
BEGIN  
   SELECT @vacationHours = VacationHours 
   FROM HumanResources.Employee
   WHERE NationalIDNumber = @employeeID
END;

Snowflake Scripting

Procedure

OUT -> SqlServer_08.sql

CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS INT)
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
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID);
      RETURN TABLE(ProcedureResultSet);
      !!!RESOLVE EWI!!! /*** SSC-EWI-TS0072 - RETURN statement will be ignored due to previous RETURN statement ***/!!!
      RETURN VACATIONHOURS;
   END;
$$;

SELF and specific user ('user_name') execution contexts are not supported in Snowflake Scripting.

READONLY AND VARYING PARAMETERS

Snowflake does not support READONLY and VARYING parameter types, an FDM is added instead.

SQL Server

Procedure

IN -> SqlServer_09.sql

CREATE OR ALTER PROCEDURE GetVacationHours
   @Param1 INT READONLY,  
   @Param2 INT VARYING
AS
BEGIN  
   SELECT * FROM Table1;
END;

Snowflake Scripting

Procedure

OUT -> SqlServer_09.sql

CREATE OR REPLACE PROCEDURE GetVacationHours (PARAM1 INT /*** SSC-FDM-0024 - FUNCTIONALITY FOR 'READONLY PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/, PARAM2 INT /*** SSC-FDM-0024 - FUNCTIONALITY FOR 'VARYING PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/)
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
         *
      FROM
         Table1);
      RETURN TABLE(ProcedureResultSet);
   END;
$$;

Known Issues

Unsupported Optional Arguments

• [VARYING] Applies only to cursor parameters.Specifies the result set supported as an output parameter. This parameter is dynamically constructed by the procedure and its contents may vary. Snowflake scripting does not support CURSOR as a valid return data type.

• [= default] Makes a parameter optional through the definition of a default value. Snowflake scripting does not natively supports default parameter values.

• [READONLY] Indicates that the parameter cannot be updated or modified within the body of the procedure. Currently unsupported in Snowflake Scripting.

• [WITH RECOMPILE] Forces the database engine to compile the stored procedure's query plan each time it is executed. Currently unsupported in Snowflake Scripting.

• [WITH ENCRYPTION] Used to encrypt the text of a stored procedure. Only users with access to system tables or database files (such as sysadmin users) will be able to access the procedure text after its creation. Currently unsupported in Snowflake Scripting.

• [FOR REPLICATION] Restricts the stored procedure to be executed only during replication. Currently unsupported in Snowflake Scripting.

Related EWIS

1. SSC-EWI-0030: The statement below has usages of dynamic SQL.

2. SSC-EWI-TS0072: RETURN statement will be ignored due to previous RETURN statement.

3. SSC-FDM-0024: Functionality is not currently supported by Snowflake Scripting.