SnowflakeDocumentation Home

DEFAULT PARAMETERS

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

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

Description

A default parameter is a parameter that has a value in case an argument is not passed in the procedure or function call. Since Snowflake doesn't support default parameters, SnowConvert inserts the default value in the procedure or function call.

In the declaration, the DEFAULT VALUE clause of the parameter is removed. Both syntaxes, the := symbol and the DEFAULT clause, are supported.

Sample Source Patterns

Sample auxiliary code

CREATE TABLE TABLE1(COL1 NUMBER, COL2 NUMBER);
CREATE TABLE TABLE2(COL1 NUMBER, COL2 NUMBER, COL2 NUMBER);0016

Default parameter declaration

Oracle

IN -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS1 (
    param1 NUMBER,
    param2 NUMBER default TO_NUMBER(1)
)
AS
BEGIN 
	INSERT INTO TABLE1 (COL1, COL2)
    VALUES(param1, param2);
END;
CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS2 (
    param1 NUMBER default 1,
    param2 NUMBER default 2
)
AS
BEGIN 
	INSERT INTO TABLE1 (COL1, COL2)
    VALUES(param1, param2);
END;

CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_DEAFAULT_PARAMS3 (
    param1 NUMBER DEFAULT 100,
    param2 NUMBER,
    param3 NUMBER DEFAULT 1000
)
IS
BEGIN
	INSERT INTO TABLE2(COL1, COL2, COL3)
    VALUES (param1, param2, param3);
END;

Snowflake Scripting

OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS1 (param1 NUMBER(38, 18),
   param2 NUMBER(38, 18) DEFAULT TO_NUMBER(1)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO TABLE1(COL1, COL2)
		   VALUES(:param1, :param2);
	END;
$$;

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS2 (
   param1 NUMBER(38, 18) DEFAULT 1,
   param2 NUMBER(38, 18) DEFAULT 2
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO TABLE1(COL1, COL2)
		   VALUES(:param1, :param2);
	END;
$$;

CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_DEAFAULT_PARAMS3 (
   param1 NUMBER(38, 18) DEFAULT 100, param2 NUMBER(38, 18),
   param3 NUMBER(38, 18) DEFAULT 1000
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO TABLE2(COL1, COL2, COL3)
		   VALUES (:param1, :param2, :param3);
	END;
$$;

Calling procedures with default parameters

Oracle

IN -> Oracle_02.sql