CREATE PROCEDURE

Translation reference to convert Transact-SQL CREATE PROCEDURE clauses to Snowflake

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Applies to

• SQL Server

• Azure Synapse Analytics

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 Transact-SQL 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 Transact-SQL. Snowflake Scripting does not allow to define procedures without a body, but the following example shows the equivalence.

Transact-SQL

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.

Transact-SQL

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.

Transact-SQL

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.

Transact-SQL

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

Transact-SQL output keyword indicates that the parameter is an output parameter, whose 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.

Transact-SQL

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 OUT INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID;
   END;
$$;

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.

Transact-SQL

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

Transact-SQL'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)

Transact-SQL

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

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "HumanResources.Employee" **
CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS OUT INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS OWNER
AS
$$
   BEGIN
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID;
   END;
$$;

• Caller

Transact-SQL

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

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "HumanResources.Employee" **
CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS OUT INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID;
   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.

Transact-SQL

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 !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'READONLY PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!, PARAM2 INT !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - 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-0058: Functionality is not currently supported by Snowflake Scripting.