RETURN statement will be ignored due to previous RETURN statement
Severity
Low
Some parts of the output code are omitted for clarity reasons.
Description
This EWI is added when SELECT statements and OUPUT parameters should be returned. In this case, the resultsets from the SELECT statements are prioritized.
Input Code:
IN -> SqlServer_01.sql
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:
OUT -> SqlServer_01.sql
CREATE OR REPLACE PROCEDURE SOMEPROC (PRODUCT_COUNT INT, _123 INT)
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
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);
--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
RETURN return_arr;
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0072 - 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.
