SSC-FDM-TS0028

Output parameters must have the same order as they appear in the executed code

Description

This FDM notifies that the output parameters in the SP_EXECUTESQL statement must be in the same order as they appear in the SQL string to execute. Otherwise, the output values will not be correctly assigned.

Code Example

Correct case

As can be seen, @MaxAgeOUT and @MaxIdOUT appear in the same order in both the SQL string and the output parameters.

Thus, when converting the code, the SELECT $1, $2 INTO :MAXAGE, :MAXID FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) will assign the values correctly.

Transact

IN -> SqlServer_01.sql

CREATE PROCEDURE CORRECT_OUTPUT_PARAMS_ORDER
AS
BEGIN
    DECLARE @MaxAge INT;
    DECLARE @MaxId INT;

    EXECUTE sp_executesql
        N'SELECT @MaxAgeOUT = max(AGE), @MaxIdOut = max(ID) FROM PERSONS WHERE ID < @id AND AGE < @age;',
        N'@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT',
        30,
        100,
        @MaxAgeOUT = @MaxAge OUTPUT,
        @MaxIdOut = @MaxId OUTPUT;

    SELECT @MaxAge, @MaxId;
END

Snowflake

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE CORRECT_OUTPUT_PARAMS_ORDER ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/07/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    MAXAGE INT;
    MAXID INT;
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF('SELECT
   MAX(AGE),
   MAX(ID) FROM
   PERSONS
WHERE
   ID < @id AND AGE < @age;', '@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT', ARRAY_CONSTRUCT('', '', 'MAXAGEOUT', 'MAXIDOUT'), ARRAY_CONSTRUCT(
    30,
    100, :MAXAGE, :MAXID));
    --** SSC-FDM-TS0028 - OUTPUT PARAMETERS MUST HAVE THE SAME ORDER AS THEY APPEAR IN THE EXECUTED CODE **
    SELECT
      $1,
      $2
    INTO
      :MAXAGE,
      :MAXID
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    ProcedureResultSet := (
    SELECT
      :MAXAGE,
      :MAXID);
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

Problematic case

As can be seen, @MaxAgeOUT and @MaxIdOUT in the output parameters appear in a different order compared to the SQL string.

Thus, when converting the code, the SELECT $1, $2 INTO :MAXID, :MAXAGE FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) will assign the values incorrectly. Max(AGE) will be assigned to :MAXID and Max(ID) to :MAXAGE.

This needs to be manually fixed by either changing the order of the output parameters in the SELECT INTO statement or by changing the order in the SQL string.

Transact

CREATE PROCEDURE INCORRECT_OUTPUT_PARAMS_ORDER
AS
BEGIN
    DECLARE @MaxAge INT;
    DECLARE @MaxId INT;

    EXECUTE sp_executesql
        N'SELECT @MaxAgeOUT = max(AGE), @MaxIdOut = max(ID) FROM PERSONS WHERE ID < @id AND AGE < @age;',
        N'@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT',
        30,
        100,
        @MaxIdOut = @MaxId OUTPUT,
        @MaxAgeOUT = @MaxAge OUTPUT;

    SELECT @MaxAge, @MaxId;
END

Snowflake

CREATE OR REPLACE PROCEDURE INCORRECT_OUTPUT_PARAMS_ORDER ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/07/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    MAXAGE INT;
    MAXID INT;
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF('SELECT
   MAX(AGE),
   MAX(ID) FROM
   PERSONS
WHERE
   ID < @id AND AGE < @age;', '@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT', ARRAY_CONSTRUCT('', '', 'MAXIDOUT', 'MAXAGEOUT'), ARRAY_CONSTRUCT(
    30,
    100, :MAXID, :MAXAGE));
    --** SSC-FDM-TS0028 - OUTPUT PARAMETERS MUST HAVE THE SAME ORDER AS THEY APPEAR IN THE EXECUTED CODE **
    SELECT
      $1,
      $2
    INTO
      :MAXID,
      :MAXAGE
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    ProcedureResultSet := (
    SELECT
      :MAXAGE,
      :MAXID);
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

Recommendations

• Make sure the OUTPUT parameters are in the same order as they appear in the SQL string.

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