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:
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:
/*** 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 ());
The source code may need to be restructured to fit with the Snowflake user-defined functions approach.
