CREATE PROCEDURE
Translation reference to convert Transact-SQL CREATE PROCEDURE clauses to Snowflake
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
CREATE PROC SampleProcedure AS;Snowflake Scripting
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
CREATE OR ALTER PROCEDURE Add_Privacy_Department
AS
EXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');Snowflake Scripting
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
ALTER PROCEDURE procedureName
AS
SELECT 1 AS ThisDB;Snowflake Scripting
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
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
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, 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.
Transact-SQL
CREATE PROCEDURE GetVacationHours
@employeeId INT,
@vacationHours INT OUTPUT
AS
BEGIN
SELECT @vacationHours = VacationHours
FROM HumanResources.Employee
WHERE NationalIDNumber = @employeeID
END;Snowflake Scripting
CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS 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;
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.
Transact-SQL
CREATE PROCEDURE OPTIONAL_PARAMETER @VAR1 INT = 1, @VAR2 INT = 2
AS
BEGIN
RETURN NULL;
END
GO
EXEC OPTIONAL_PARAMETER @VAR2 = 4Snowflake Scripting
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
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
CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS OWNER
AS
$$
BEGIN
SELECT
VacationHours
INTO
:VACATIONHOURS
FROM
HumanResources.Employee
WHERE
NationalIDNumber = :EMPLOYEEID;
RETURN VACATIONHOURS;
END;
$$;Caller
Transact-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
CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS 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;
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.
Transact-SQL
CREATE OR ALTER PROCEDURE GetVacationHours
@Param1 INT READONLY,
@Param2 INT VARYING
AS
BEGIN
SELECT * FROM Table1;
END;Snowflake Scripting
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
SSC-EWI-0030: The statement below has usages of dynamic SQL.
SSC-EWI-0058: Functionality is not currently supported by Snowflake Scripting.
Last updated