OUTPUT PARAMETERS

This article is about the current transformation of the output parameters and how their functionality is being emulated.

Some parts in the output code are omitted for clarity reasons.

Description

An output parameter is a parameter whose value is passed out of the stored procedure, back to the calling SQL block. Since the output parameters are not supported by Snowflake Scripting, a solution has been implemented in order to emulate their functionality.

Sample Source Patterns

Single OUT parameter

The most basic scenario for OUT parameters is when the procedure only has one. In this case, we simply return the OUT parameter at the end of the procedure body.

The EXEC procedure has to be translated as well, for this a CALL is created, the parameters are passed without any modifier ("OUT" is removed), and subsequently, an assignment is done so the parameter is associated with it's respective resulting value.

SQL Server

Query

IN -> SqlServer_01.sql

-- Procedure with output parameter
CREATE PROCEDURE dbo.outmain
@name VARCHAR (255) OUTPUT
AS
SET @name = 'Jane';

-- Auxiliary procedure that calls the main procedure
CREATE PROCEDURE dbo.outaux
AS
DECLARE @name VARCHAR (255);
EXEC dbo.outmain
    @name = @name OUTPUT;

Snowflake Scripting

Query

OUT -> SqlServer_01.sql

-- Procedure with output parameter
CREATE OR REPLACE PROCEDURE dbo.outmain (NAME STRING)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        NAME := 'Jane';

        -- Auxiliary procedure that calls the main procedure
        !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CREATE PROCEDURE' NODE ***/!!!
        CREATE PROCEDURE dbo.outaux
AS
DECLARE @name VARCHAR (255);
EXEC dbo.outmain
    @name = @name OUTPUT;
        RETURN NAME;
    END;
$$;

Multiple OUT parameters

When more than one OUT parameters are found, the RETURNS clause of the procedure changes to VARIANT. This is to accommodate the OBJECT_CONSTRUCT that is going to be used to store the values of the OUT parameters.

On top of that, a RETURN statement is added to the end of the procedure's body. This is where the OBJECT_COSNTRUCT is created and all the OUT parameter values are stored within it. This object will then be used by the caller to assign the parameters value to the corresponding result.

SQL Server

IN -> SqlServer_02.sql

CREATE OR ALTER PROCEDURE basicProc (
    @col1 INT OUT,
    @col2 VARCHAR(10) OUT
) AS
BEGIN
    SET @col1 = 4;
    SET @col2 = 'test';
END;

CREATE OR ALTER PROCEDURE basicProcCall AS
BEGIN
    DECLARE @var1 INT = 0;
    DECLARE @var2 VARCHAR(10) = 'EMPTY';

    EXEC basicProc @var1 OUT, @var2 OUT;
    INSERT INTO TABLE1(col1, col2) VALUES (@var1, @var2);
END;

EXEC basicProcCall;

Snowflake Scripting

OUT -> SqlServer_02.sql

CREATE OR REPLACE PROCEDURE basicProc (COL1 INT, COL2 STRING)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        BEGIN
            COL1 := 4;
            COL2 := 'test';
        END;
        !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CREATE PROCEDURE' NODE ***/!!!
        CREATE OR ALTER PROCEDURE basicProcCall AS
BEGIN
    DECLARE @var1 INT = 0;
    DECLARE @var2 VARCHAR(10) = 'EMPTY';

    EXEC basicProc @var1 OUT, @var2 OUT;
    INSERT INTO TABLE1(col1, col2) VALUES (@var1, @var2);
END;

EXEC basicProcCall;
        RETURN OBJECT_CONSTRUCT('COL1', :COL1, 'COL2', :COL2);
    END;
$$;

OUT parameters and return values

SQL Server allows procedures to have return values. When a procedure has both a return value and OUT parameter(s), a similar approach to the Multiple OUT parameters scenario is followed. The original return value is treated as an OUT parameter would be treated, so it's stored within the OBJECT_CONSTRUCT and extracted inside the caller procedure.

SQL Server

Query

IN -> SqlServer_03.sql

-- Procedure with multiple output parameters
CREATE PROCEDURE dbo.outmain
@name VARCHAR (255) OUTPUT
AS
SET @name = 'Jane';
RETURN 0;

-- Auxiliary procedure that calls the main procedure
CREATE PROCEDURE dbo.outaux
AS
DECLARE @name VARCHAR (255);
DECLARE @returnValue INT;
EXEC @returnValue = dbo.outmain
    @name = @name OUTPUT;

Snowflake Scripting

Query

OUT -> SqlServer_03.sql

-- Procedure with multiple output parameters
CREATE OR REPLACE PROCEDURE dbo.outmain (NAME STRING)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        NAME := 'Jane';
        RETURN OBJECT_CONSTRUCT('SC_RET_VALUE', 0, 'NAME', :NAME);

        -- Auxiliary procedure that calls the main procedure
        !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CREATE PROCEDURE' NODE ***/!!!
        CREATE PROCEDURE dbo.outaux
AS
DECLARE @name VARCHAR (255);
DECLARE @returnValue INT;
EXEC @returnValue = dbo.outmain
    @name = @name OUTPUT;
    END;
$$;

Customer data type OUT parameters

when the output parameter is a customer type, the process is similar to a regular data type.

SQL Server

IN -> SqlServer_04.sql

CREATE PROCEDURE procedure_udtype_out_params(
  @p_employee_id INT,
  @p_phone [dbo].[PhoneNumber] OUTPUT
) AS
BEGIN
  SELECT @p_phone = phone
  FROM employees
  WHERE employee_id = @p_employee_id;
END;

Snowflake Scripting

OUT -> SqlServer_04.sql

CREATE OR REPLACE PROCEDURE procedure_udtype_out_params (P_EMPLOYEE_ID INT, P_PHONE dbo.PhoneNumber !!!RESOLVE EWI!!! /*** SSC-EWI-TS0059 - DATA TYPE DBO.PHONENUMBER IS NOT SUPPORTED IN SNOWFLAKE ***/!!!)
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    ProcedureResultSet RESULTSET;
  BEGIN
    ProcedureResultSet := (
    SELECT
      phone
    INTO
      :P_PHONE
    FROM
      employees
    WHERE
      employee_id = :P_EMPLOYEE_ID);
    RETURN TABLE(ProcedureResultSet);
    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0072 - RETURN statement will be ignored due to previous RETURN statement ***/!!!
    RETURN P_PHONE;
  END;
$$;

Related EWIs

1. SSC-EWI-0073: Pending Functional Equivalence Review.

2. SSC-EWI-TS0072: RETURN statement will be ignored due to previous RETURN statement.