MSCEWI1068

User defined function was transformed to a Snowflake procedure.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

Snowflake user defined functions do not support the same features as Oracle or SQL Server. To maintain the functional equivalence the function is transformed to a Snowflake stored procedure. This will affect their usage in queries.

Example Code

1. Scalar Return Type

Input Code:

Oracle

CREATE OR REPLACE FUNCTION FUNC01(x NUMBER) RETURN NUMBER AS
  VAR1 NUMBER;
  BEGIN
   -- some pl sql statements
   RETURN VAR1;
  END FUNC01;

SQL Server

CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS INT
AS
BEGIN
    DECLARE @i int = 0, @p int;
    Select @p = COUNT(*) FROM PURCHASING.VENDOR
    
    WHILE (@p < 1000)
    BEGIN
        SET @i = @i + 1
        SET @p = @p + @i
    END
        
    IF (@i = 6)
        RETURN 1
    
    RETURN @p
END

Output Code:

From Oracle

/*** MSC-WARNING - MSCEWI1068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/
CREATE OR REPLACE PROCEDURE PUBLIC.FUNC01(x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   /* mobilize helpers */
   /* pl sql statements translation */
   return VAR1;
$$;

From SQL Server

/*** MSC-WARNING - MSCEWI1068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/
CREATE OR REPLACE PROCEDURE PURCHASING.FOO()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
      // some implementations here...
   // END REGION

   let I = 0;
   let P;
   SELECT(`COUNT(*) FROM PURCHASING.VENDOR`,[],(value) => P = value);
   while ( P < 1000 ) {
      I = I + 1;
      P = P + I;
   }
   if (I == 6) {
      return 1;
   }
   return P;
$$;

2. Table-Valued return type

Input code from SQL Server:

CREATE OR ALTER FUNCTION getGroupName
(@departmentId INT)
RETURNS @groupNames TABLE (groupName VARCHAR(15))
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'  
WHILE @name = 'Another Department'   
BEGIN    
	IF (@departmentId < 3)
	BEGIN
		SET @name = 'engineering'
	END

	IF @departmentId = 3
	BEGIN
		SET @name = 'Tool Design'
	END

	SELECT @departmentId = @departmentId / 3
END
INSERT @groupNames SELECT @name

RETURN
END

Output code:

CREATE OR REPLACE PROCEDURE getGroupName(departmentId FLOAT) 
RETURNS VARIANT NOT NULL 
LANGUAGE JAVASCRIPT 
AS 
$$ 
    var sqlCommand = "CREATE OR REPLACE TEMPORARY TABLE groupNames (group_name VARCHAR(30));"; 
    var stmt = snowflake.createStatement( {sqlText: sqlCommand} ); 
    var resultSet = stmt.execute(); 
    resultSet.next(); 
    
    let name = 'Another department'
    while (name == 'Another department'){
      if (DEPARTMENTID < 3){
          name = 'engineering'
      }
      if (DEPARTMENTID == 3){
          name = 'Tool Design'
      }
      DEPARTMENTID = DEPARTMENTID / 3
    }
    
    sqlCommand = `INSERT INTO groupNames VALUES(:1);` 
    result = snowflake.execute(
        { 
        sqlText: sqlCommand, 
        binds: [name] 
        }
        );         
    
    var sqlCommand = "SELECT * FROM groupNames"; 
    var stmt = snowflake.createStatement( {sqlText: sqlCommand} ); 
    var resultSet = stmt.execute(); 
    resultSet.next(); 
    var arrayOfRows = [];
    arrayOfRows.push(resultSet.getColumnValue(1));
    
    return arrayOfRows;
$$; 

Recommendations

• Separate the queries to maintain the same logic as in the example below.

Input code from SQL Server:

CREATE OR ALTER FUNCTION getGroupName
(@departmentId INT)
RETURNS @groupNames TABLE (groupName VARCHAR(15))
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'  
WHILE @name = 'Another Department'   
BEGIN    
	IF (@departmentId < 3)
	BEGIN
		SET @name = 'engineering'
	END
	  
	IF @departmentId = 3
	BEGIN
		SET @name = 'Tool Design'
	END
	
	SELECT @departmentId = @departmentId / 3
END
INSERT @groupNames SELECT @name
RETURN
END

SELECT * FROM getGroupName(9);

Output code:

CREATE OR REPLACE TEMPORARY TABLE groupNames (groupName VARCHAR(30));

CREATE OR REPLACE FUNCTION funcGetGroupName(departmentId FLOAT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS 
$$
let name = 'Another department'
  while (name == 'Another department'){
      if (DEPARTMENTID < 3){
          name = 'engineering'
      }
      if (DEPARTMENTID == 3){
          name = 'Tool Design'
      }
      DEPARTMENTID = DEPARTMENTID / 3
   }
  return name
$$;

SET col = (SELECT funcGetGroupName(9));

INSERT INTO groupNames SELECT $col;

CREATE OR REPLACE FUNCTION getGroupName ()
RETURNS TABLE(groupName VARCHAR(30))
AS
$$   
    SELECT * FROM groupNames
$$;

SELECT * FROM TABLE(getGroupName ());

• The source code may need to be restructured to fit with the Snowflake user-defined functions approach.

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