OUTPUT PARAMETERS

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

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

-- 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

-- Procedure with output parameter
CREATE OR REPLACE PROCEDURE dbo.outmain (NAME STRING /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **
        NAME := 'Jane';
    END;
    RETURN NAME;
$$;

-- Auxiliary procedure that calls the main procedure
CREATE OR REPLACE PROCEDURE dbo.outaux ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        NAME VARCHAR(255);
    BEGIN
         
        CALL dbo.outmain(:NAME)
        /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
        NAME := (SELECT
            *
        FROM
            TABLE(RESULT_SCAN(LAST_QUERY_ID())));
    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

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

CREATE OR REPLACE PROCEDURE PUBLIC.basicProc (COL1 FLOAT /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/, COL2 STRING /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        COL1 := 4;
        COL2 := 'test';
        /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
        RETURN OBJECT_CONSTRUCT('COL1', :COL1, 'COL2', :COL2);
    END;
$$;

CREATE OR REPLACE PROCEDURE PUBLIC.basicProcCall ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        VAR1 INT := 0;
        VAR2 VARCHAR(10) := 'EMPTY';
        call_results VARIANT;
    BEGIN
        call_results := (CALL basicProc(:VAR1, :VAR2));
        /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
        VAR1 := :call_results:col1;
        VAR2 := :call_results:col2;
        INSERT INTO PUBLIC.TABLE1 (col1, col2) VALUES (:VAR1, :VAR2);
    END;
$$;

CALL basicProcCall();

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

-- 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

-- Procedure with multiple output parameters
CREATE OR REPLACE PROCEDURE dbo.outmain (NAME STRING /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        NAME := 'Jane';
        /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
        RETURN OBJECT_CONSTRUCT('SC_RET_VALUE', 0, 'NAME', :NAME);
    END;
$$;

-- Auxiliary procedure that calls the main procedure
CREATE OR REPLACE PROCEDURE dbo.outaux ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        NAME VARCHAR(255);
        RETURNVALUE INT;
    BEGIN
        CALL dbo.outmain(:NAME)
        /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
        LET call_results VARIANT := (SELECT
            *
        FROM
            TABLE(RESULT_SCAN(LAST_QUERY_ID())));
        RETURNVALUE := GET(:call_results, 'SC_RET_VALUE');
        NAME := GET(:call_results, 'NAME');
    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

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

CREATE OR REPLACE PROCEDURE procedure_udtype_out_params (P_EMPLOYEE_ID INT, P_PHONE dbo.PhoneNumber /*** MSC-ERROR - MSCEWI4059 - DATA TYPE DBO.PHONENUMBER IS NOT SUPPORTED IN SNOWFLAKE ***/ /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/)
RETURNS TABLE()
LANGUAGE SQL
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);
    --** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **
    --** MSC-WARNING - MSCEWI4072 - RETURN statement will be ignored due to previous RETURN statement **
    RETURN P_PHONE;
  END;
$$;

Related EWIs

1. MSCEWI1082: Auto-Generated code in order to emulate the output parameters functionality.
2. MSCEWI1083: Output parameters are not supported by Snowflake Scripting but their functionality is being emulated.