Links

MSCEWI1071

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

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]