OUTPUT PARAMETERS

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Applies to

• SQL Server

• Azure Synapse Analytics

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.

Transact-SQL

Query

IN -> SqlServer_01.sql

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

GO 

-- 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 OUT STRING)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        NAME := 'Jane';
    END;
$$;

-- Auxiliary procedure that calls the main procedure
CREATE OR REPLACE PROCEDURE dbo.outaux ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        NAME VARCHAR(255);
    BEGIN
         
        CALL dbo.outmain(: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.

Transact-SQL

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;

GO

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;

GO

EXEC basicProcCall;

Snowflake Scripting

OUT -> SqlServer_02.sql

CREATE OR REPLACE PROCEDURE basicProc (COL1 OUT INT, COL2 OUT STRING)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        COL1 := 4;
        COL2 := 'test';
    END;
$$;

CREATE OR REPLACE PROCEDURE basicProcCall ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        VAR1 INT := 0;
        VAR2 VARCHAR(10) := 'EMPTY';
    BEGIN
         
         
        CALL basicProc(:VAR1, :VAR2);
        INSERT INTO TABLE1 (col1, col2) VALUES (:VAR1, :VAR2);
    END;
$$;

CALL basicProcCall();

OUT parameters and return values

Transact-SQL 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.

Transact-SQL

Query

IN -> SqlServer_03.sql

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

GO

-- 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 OUT STRING)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        NAME := 'Jane';
        RETURN 0;
    END;
$$;

-- Auxiliary procedure that calls the main procedure
CREATE OR REPLACE PROCEDURE dbo.outaux ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        NAME VARCHAR(255);
        RETURNVALUE INT;
    BEGIN
         
         
        CALL dbo.outmain(:NAME);
    END;
$$;

Customer data type OUT parameters

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

Transact-SQL

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

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "[dbo].[PhoneNumber]", "employees" **
CREATE OR REPLACE PROCEDURE procedure_udtype_out_params (P_EMPLOYEE_ID INT, P_PHONE OUT VARIANT /*** SSC-FDM-TS0015 - DATA TYPE DBO.PHONENUMBER IS NOT SUPPORTED IN SNOWFLAKE ***/)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    SELECT
      phone
    INTO
      :P_PHONE
    FROM
      employees
    WHERE
      employee_id = :P_EMPLOYEE_ID;
  END;
$$;

Known Issues

No issues were found.

Related EWIs

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

2. SSC-FDM-TS0015: Data type is not supported in Snowflake.