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.
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.