Links

MSCEWI1068

User defined function was transformed to a Snowflake procedure.

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
SQL Server
CREATE OR REPLACE FUNCTION FUNC01(x NUMBER) RETURN NUMBER AS
VAR1 NUMBER;
BEGIN
-- some pl sql statements
RETURN VAR1;
END FUNC01;
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
From SQL Server
/*** 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;
$$;
/*** 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 ());
  • Source code may need to be restructured to fit with the Snowflake user defined functions approach.
  • For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.