MSCCP0008

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.

• If you need more support, you can email us at [email protected]