SSC-EWI-TS0049

Multiple Line If Body translation planned to be delivered in the future.

Some parts in the output code are omitted for clarity reasons.

Severity

Medium

Description

Most of the cases inIF statements that contain a Begin ... End block inside their body are supported. This is a successful scenario (no SSC-EWI-TS0049 generated).

Code Example

Input Code:

IN -> SqlServer_01.sql

CREATE OR ALTER FUNCTION [PURCHASING].[FOO](@status INT)
Returns INT
As
Begin
    declare @result as int = 10;
    SELECT @result = quantity FROM TABLE1 WHERE COL1 = @status;
    IF @result = 3
    BEGIN
        IF @result>0 SELECT @result=0  ELSE SELECT @result=1
        SELECT @result = 1 
    END
    return @result;
End

Output Code:

OUT -> SqlServer_01.sql

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO (STATUS INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        RESULT INT := 10;
    BEGIN
         
        SELECT
            quantity
        INTO
            :RESULT
        FROM
            TABLE1
        WHERE
            COL1 = :STATUS;
        IF (:RESULT = 3) THEN
            BEGIN
                IF (:RESULT >0) THEN SELECT
                        0
                    INTO
                        :RESULT;
                ELSE
                    SELECT
                        1
                    INTO
                        :RESULT;
                END IF;
        SELECT
                    1
                INTO
                    :RESULT;
            END;
        END IF;
        RETURN :RESULT;
    END;
$$;

In a general code example (Like the on top) the conversion is done successfully. But there are some edge cases where the "IF" statement is not converted and the EWI will be generated.

Manual Support

Case 1: Single Statement

For these cases, the transformation would be straightforward, since the transformed statement would appear in a select clause

IF @result = 0
BEGIN 
    SET @result =1
END

CASE WHEN (SELECT RESULT FROM CTE2)= 0 THEN
( SELECT 1 AS RESULT )

Case 2: Multiple Statements

For cases in which multiple statements are being transformed, we should transform the N Statement, and use it as the source table for the N+1 Statement.

IF @result = 0
BEGIN 
    Statement1
    Statement2
    Statement3
END

CASE WHEN (SELECT RESULT FROM CTE2)= 0 THEN
(
    SELECT TransformedStatement3
    FROM (
        SELECT TransformedStatement2
        FROM (
            SELECT TransformedStatement1
        ) T1
    ) T2
)

Case 3: Multiple set statements

For these cases, it will be necessary to replicate a transformation for each set statement.

IF @result = 0
BEGIN 
    SET @var1 = 1
    SET @var2 = 3
    SET @var3 = @var2
END

WITH CTE1 AS (
    SELECT 
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0) = 0 THEN
        (SELECT 1) AS VAR1)
WITH CTE2 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0)= 0 THEN
        (SELECT 3) AS VAR2)
WITH CTE3 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0)= 0 THEN
        (SELECT T1.VAR2 
        FROM ((SELECT 3) AS VAR2) AS T1) AS VAR3) 
...

Recommendations

• If you need more support, you can email us at [e-mail]