This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.
Severity
Medium
Description
A variable value has been overwritten by a new value. The previous value may be inaccesible and may not be functionally equivalent.
Code Example
Input Code:
CREATE OR ALTER FUNCTION PURCHASING.FOO2()
RETURNS MONEY
AS
BEGIN
declare @firstValue MONEY
declare @secondValue MONEY
declare @Result MONEY
select @Result = 0
select @firstValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 1
select @secondValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 2
if @firstValue is not null
select @Result = @firstValue
if @secondValue is not null
select @Result = @secondValue
return @Result
END
Output Code:
/*** MSC-WARNING - MSCEWI1069 - USER DEFINED FUNCTION INNER LOGIC WAS TRANSFORMED TO A SINGLE COMMON TABLE EXPRESSION ***/
CREATE OR REPLACE FUNCTION PURCHASING.FOO2 ()
RETURNS DOUBLE PRECISION
LANGUAGE SQL
AS
$$
WITH CTE1 AS (
select 0 AS RESULT), CTE2 AS (
select SubTotal AS FIRSTVALUE from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 1), CTE3 AS (
select SubTotal AS SECONDVALUE from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 2), CTE5 AS (SELECT
CASE
WHEN (SELECT FIRSTVALUE FROM CTE2) is not null THEN (
select --** MSC-ERROR - MSCEWI4040 - VARIABLE HAS OVERWRITTEN A PREVIOUS VALUE, CHECK EQUIVALENCE **
(SELECT FIRSTVALUE FROM CTE2) AS RESULT) END), CTE7 AS (SELECT
CASE
WHEN (SELECT SECONDVALUE FROM CTE3) is not null THEN (
select --** MSC-ERROR - MSCEWI4040 - VARIABLE HAS OVERWRITTEN A PREVIOUS VALUE, CHECK EQUIVALENCE **
(SELECT SECONDVALUE FROM CTE3) AS RESULT) END) SELECT RESULT FROM CTE7
$$;
