MSCEWI4028

Labels inside user-defined function were removed

Severity

Low

Description

This EWI is added when there are LABEL and GOTO usages in an user-defined function body. The statements will be removed because Snowflake does not support labels.

The labels content will be added instead of the GOTO instances calling a label.

Code Example

Input Code:

CREATE OR ALTER FUNCTION [PURCHASING].[FOO]
(
	 @BUSINESSENTITYID INT
)
returns varchar(50)
AS
BEGIN	
	DECLARE @result varchar(50)
	SELECT @result = v.AccountNumber FROM PURCHASING.VENDOR v WHERE v.BUSINESSENTITYID = @BUSINESSENTITYID
	
	IF @result = 'ALLENSON0001'
	BEGIN
		GOTO ExitF
	END
		
	ExitF:			
	RETURN @result 
END

Output Code:

CREATE OR REPLACE FUNCTION PURCHASING.FOO
(
	BUSINESSENTITYID INT
)
RETURNS  varchar(50)
AS 
$$
	WITH CTE1 AS (
		SELECT v.AccountNumber result FROM PURCHASING.VENDOR v WHERE v.BUSINESSENTITYID = BUSINESSENTITYID
	)
	SELECT CASE 
		WHEN (SELECT RESULT FROM CTE1) = 'ALLENSON0001'
		THEN (
			/*** MSC-WARNING - MSCEWI4028 - LABELS ARE NOT SUPPORTED IN SNOWFLAKE, ADDED THE LABEL CONTENT INSTEAD ***/;
			SELECT RESULT FROM CTE1
		)
		ELSE (SELECT RESULT FROM CTE1)
		END AS RESULT
$$;

Recommendations