Multiple Line If Body translation planned to be delivered in the future.
This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.
Severity
Medium
Description
Translation for if statements that contain a Begin ... End block inside their body is not supported yet for the entire statement block. Instead, this EWI and a Select Null are added.
Code Example
Input Code:
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:
CREATE OR REPLACE FUNCTION PURCHASING.FOO (STATUS FLOAT)
Returns INT
LANGUAGE SQL
AS
$$
WITH CTE1 AS
(
SELECT
10 AS RESULT
),
CTE2 AS
(
SELECT
quantity AS RESULT
FROM
TESTS.TABLE1
WHERE
COL1 = STATUS
),
CTE6 AS
(
SELECT
CASE
WHEN (
SELECT
RESULT
FROM
CTE2
) = 3
THEN (SELECT
/*** MSC-ERROR - MSCEWI4049 - TRANSLATION FOR MULTIPLE LINE IF BODY IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
/*SELECT
@result = 1*/
null
FROM
(
SELECT
/*** MSC-ERROR - MSCEWI4049 - TRANSLATION FOR MULTIPLE LINE IF BODY IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
/*IF @result > 0
SELECT
@result = 0
ELSE
SELECT
@result = 1*/
null
) T1)
END AS RESULT
)
SELECT
RESULT
FROM
CTE6
$$;
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)
...
