CREATE PROCEDURE
Translation reference to convert SQL Server 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 SQL Server EXECUTE, check here.
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
Snowflake Scripting
Basic stored procedure
The following example details a simple stored procedure that will include a new Privacy department into the AdventureWorks2019 database.
SQL Server
Snowflake Scripting
Alter procedure
The transformation for the ALTER procedure is equivalent to the basic procedure.
SQL Server
Snowflake Scripting
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
Snowflake Scripting
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
Snowflake Scripting
Only 1 output parameter can be returned from a Snowflake scripting stored procedure.
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
Snowflake Scripting
Caller
SQL Server
Snowflake Scripting
SELF and specific user ('user_name') execution contexts are not supported in Snowflake Scripting.
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
MSCEWI1058: Functionality is not currently supported by Snowflake Scripting
Last updated