This is a deprecated version of the SnowConvert documentation, please visit the official site .
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 .