MSCEWI4072

RETURN statement will be ignored due to previous RETURN statement

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

Severity

Low

Description

This EWI is added when there are SELECT statements and OUPUT parameters that should be returned. In this case, the resultsets from the SELECT statements are prioritized.

Input Code:

CREATE PROCEDURE SOMEPROC(@product_count INT OUTPUT,  @123 INT OUTPUT)
AS
BEGIN
		SELECT * from AdventureWorks.HumanResources.Department;
        SELECT * from AdventureWorks.HumanResources.Employee;
END

Output Code:

CREATE OR REPLACE PROCEDURE SOMEPROC (PRODUCT_COUNT FLOAT /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/, _123 FLOAT /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/)
RETURNS ARRAY
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        ProcedureResultSet1 VARCHAR;
        ProcedureResultSet2 VARCHAR;
        return_arr ARRAY := array_construct();
    BEGIN
        ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
            SELECT
                *
            from
                AdventureWorks.HumanResources.Department;
        return_arr := array_append(return_arr, :ProcedureResultSet1);
        ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
            SELECT
                *
            from
                AdventureWorks.HumanResources.Employee;
        return_arr := array_append(return_arr, :ProcedureResultSet2);
        --** MSC-WARNING - MSCEWI1100 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
        RETURN return_arr;
        --** MSC-WARNING - MSCEWI4072 - RETURN statement will be ignored due to previous RETURN statement **
        RETURN OBJECT_CONSTRUCT('PRODUCT_COUNT', :PRODUCT_COUNT, '_123', :_123);
    END;
$$;

Recommendations

• Remove the RETURN statement that should be ignored.

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