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.
The following example details a simple stored procedure that will include a new Privacy department into the AdventureWorks2019 database.
SQL Server
IN -> SqlServer_02.sql
CREATEORALTERPROCEDURE Add_Privacy_DepartmentASEXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');
Snowflake Scripting
OUT -> SqlServer_02.sql
CREATEORREPLACEPROCEDURE Add_Privacy_Department ()RETURNSVARCHARLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'EXECUTEASCALLERAS$$BEGIN !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTEIMMEDIATE'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.
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
IN -> SqlServer_04.sql
CREATEORALTERPROCEDURE SetNewPrice @ProductID INT, @NewPrice MONEYASBEGINDECLARE @dynSqlStatement ASVARCHAR(300);SET @dynSqlStatement ='UPDATE Production.ProductListPriceHistory SET ListPrice = '+CAST(@NewPrice ASVARCHAR(10)) +' WHERE ProductID = '+CAST(@ProductID ASVARCHAR(10)) +' AND EndDate IS NULL';EXECUTE (@dynSqlStatement);END;
Snowflake Scripting
OUT -> SqlServer_04.sql
CREATEORREPLACEPROCEDURE SetNewPrice (PRODUCTID INT, NEWPRICE NUMBER(38, 4))RETURNSVARCHARLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'EXECUTEASCALLERAS$$DECLARE DYNSQLSTATEMENT VARCHAR(300);BEGIN DYNSQLSTATEMENT :='UPDATE Production.ProductListPriceHistory SET ListPrice = '||CAST(:NEWPRICE ASVARCHAR(10)) ||' WHERE ProductID = '||CAST(:PRODUCTID ASVARCHAR(10)) ||' AND EndDate IS NULL;'; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTEIMMEDIATE :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.
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'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.
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
IN -> SqlServer_09.sql
CREATEORALTERPROCEDURE GetVacationHours @Param1 INTREADONLY, @Param2 INT VARYINGASBEGINSELECT*FROM Table1;END;
Snowflake Scripting
OUT -> SqlServer_09.sql
CREATEORREPLACEPROCEDURE 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 ***/!!!)RETURNSTABLE()LANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'EXECUTEASCALLERAS$$DECLARE ProcedureResultSet RESULTSET;BEGIN ProcedureResultSet := (SELECT*FROM Table1);RETURNTABLE(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.