MSCEWI2007

Output parameter translated as a return statement.

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.
For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.

MSCEWI2006

MSCEWI2008

Last modified 1yr ago