MSCEWI1069

User defined function inner logic was transformed to a single Common Table Expression

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 multiple statements like DECLARE, SET, IF/ELSE, etc in their bodies. The approach of using a single COMMON TABLE EXPRESSION, helps to keep the functional equivalence of the original user-defined function and preserve its functionallity when it is called inside queries.

Example Code

SQL Server

CREATE OR ALTER FUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)
RETURNS VARCHAR(10) AS
BEGIN
	DECLARE @result VARCHAR(10)
	DECLARE @ActiveFlag BIT
	
	SELECT @ActiveFlag = ActiveFlag from PURCHASING.VENDOR v where v.BUSINESSENTITYID = @BusinessEntityID
	
	IF @ActiveFlag = 1 SET @result = 'YES'
	ELSE IF @ActiveFlag = 0 SET @result = 'NO'
	
	RETURN @result
END

Snowflake

/*** MSC-WARNING - MSCEWI1069 - USER DEFINED FUNCTION INNER LOGIC WAS TRANSFORMED TO A SINGLE COMMON TABLE EXPRESSION ***/
CREATE OR REPLACE FUNCTION PURCHASING.HasActiveFlag (
	BusinessEntityID int
)
RETURNS VARCHAR(10)
AS
$$
    WITH
	CTE1 as (SELECT ActiveFlag AS ActiveFlag 
		from PURCHASING.VENDOR v
		where v.BUSINESSENTITYID = BusinessEntityID),
	CTE2 as (SELECT CASE 
	        	WHEN (SELECT ActiveFlag from CTE1) = 1 THEN 'YES'
          		WHEN (SELECT ActiveFlag from CTE1) = 0 THEN 'NO' 
        	    	end as result)
	SELECT result FROM CTE2
$$;

Recommendations

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