Nested function/procedure declarations are considered a complex pattern and not supported in snowflake.
This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.
Severity
Low
Description
Snowflake does not support the declaration of nested functions/procedures, this warning is added to any create function or create procedure statement in which nested declarations were found.
Code example
Input
CREATE OR REPLACE FUNCTION myFunction
RETURN INTEGER
IS
total_count INTEGER;
-- Function Declaration
FUNCTION function_declaration(param1 VARCHAR) RETURN INTEGER;
FUNCTION function_definition
RETURN INTEGER
IS
count INTEGER;
PROCEDURE procedure_declaration(param1 INTEGER)
IS
BEGIN
NULL;
END;
BEGIN
RETURN count;
end;
BEGIN
-- Your logic to calculate the total employee count goes here
RETURN total_count;
END;
Output
/*** MSC-WARNING - MSCCP0008 - NESTED FUNCTION/PROCEDURE DECLARATIONS ARE CONSIDERED A COMPLEX PATTERN AND NOT SUPPORTED IN SNOWFLAKE. ***/
CREATE OR REPLACE FUNCTION myFunction ()
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
// REGION SnowConvert Helpers Code
var RAISE = function (code,name,message) {
message === undefined && ([name,message] = [message,name])
var error = new Error(message);
error.name = name
SQLERRM = `${(SQLCODE = (error.code = code))}: ${message}`
throw error;
};
// END REGION
let TOTAL_COUNT;
/* ** MSC-ERROR - MSCEWI3057 - TRANSLATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED YET ** */
/* -- Function Declaration
FUNCTION function_declaration(param1 VARCHAR) RETURN INTEGER; */
// Function Declaration
;
/* ** MSC-ERROR - MSCEWI3057 - TRANSLATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED YET ** */
/* FUNCTION function_definition
RETURN INTEGER
IS
count INTEGER;
PROCEDURE procedure_declaration(param1 INTEGER)
IS
BEGIN
NULL;
END;
BEGIN
RETURN count;
end; */
;
// Your logic to calculate the total employee count goes here
return TOTAL_COUNT;
$$;
Recommendations
Remove the nested declarations from the function/procedure.
