SSC-EWI-TS0072

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.

• If you need more support, you can email us at [e-mail]

Related EWI

1. SSC-FDM-0020: Multiple result sets are returned in temporary tables;