MSCEWI1071
User defined function structured block was transformed to a single SQL Query
Low
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.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
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
$$;
- No additional user actions are required.
Last modified 3mo ago