MSCEWI2007

Output parameter translated as a return statement.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is displayed when a Stored Procedure includes parameters with modifiers such as OUT or INOUT. Those types of parameters are translated as a return statement in the Stored Procedure body in which an array will be returned with each output value in it. The return statements are being cast to a string data type.

The parameters with modifiers INOUT have a dual behavior in Teradata. Since there is not an equivalent in Snowflake, they are translated as OUT parameters and always returned. This may cause that the target procedure returns null in cases where the INOUT was expected to behave as an IN one.

Code Example

Input Code:

CREATE PROCEDURE sampleProcedure(IN p1 INTEGER, INOUT p2 INTEGER, OUT p3 INTEGER)
BEGIN
   SET p3 = p1;
   SET p2 = p2 + 1;
END;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.sampleProcedure (P1 FLOAT, /*** MSC-WARNING - MSCEWI2007 - INOUT PARAMETER 'P2' TRANSLATED AS A RETURN STATEMENT ***/
 P2 FLOAT, /*** MSC-WARNING - MSCEWI2007 - OUT PARAMETER 'P3' TRANSLATED AS A RETURN STATEMENT ***/
 P3 FLOAT)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
	// Some Javascript code here.

	P3 = P1;
	P2 = P2 + 1;
	return PROCRESULTS(P2,P3);
 
$$;

Recommendations

• Instead of using INOUT modifier, it is recommended to use IN or OUT to be more specific with the parameter role, in order to avoid null returns in the target procedure.

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