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 .

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.

PreviousMSCCP0007 NextMSCCP0009

Last updated 1 year ago

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;

/*** 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; $$;