MSCEWI4040

Value overwritten, check function equivalence.

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
$$;

Recommendations:

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