MSCEWI1071

User defined function structured block was transformed to a single SQL Query

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This EWI appears when there is an structured block nested in one IF/ELSE or BEGIN/END statement. This just warns that the transformed block is functionally equivalent, but that could have performance issues depending on the code complexity.

Example Code

SQL Server

CREATE or ALTER FUNCTION PURCHASING.FOO ()
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @VendorId AS int;
    DECLARE @AccountNumber AS VARCHAR(50);
    
    IF 1 = 1
    BEGIN
        SELECT @VendorId = poh.VendorID 
        FROM Purchasing.PurchaseOrderHeader poh
        WHERE PurchaseOrderID = 1
    
        SELECT @AccountNumber = v.AccountNumber
        FROM Purchasing.Vendor v
        WHERE v.BusinessEntityID = @VendorId
    END
    
    RETURN @AccountNumber
END

Snowflake

CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS VARCHAR(50)
AS
$$
    WITH
	CTE1 as (
		SELECT CASE 
			/*** MSC-WARNING - MSCEWI1069 - USER DEFINED FUNCTION STRUCTURED BLOCK WAS TRANSFORMED TO A SINGLE SQL QUERY ***/
			WHEN 1 = 1
			THEN (
				SELECT V.AccountNumber AccountNumber
				FROM (SELECT poh.VendorID VendorId 
         			      FROM Purchasing.PurchaseOrderHeader poh
         			      WHERE PurchaseOrderID = 1
				) T1, Purchasing.Vendor v
				WHERE v.BusinessEntityID = T1.VendorId
			)
			END AccountNumber 
	)
	SELECT AccountNumber FROM CTE1
$$;

Recommendations

• No additional user actions are required.

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