Add Optional Parameters to Teradata Stored Procedure

Question

Is there a way to add optional parameters to a stored procedure in Teradata?

I know in Oracle it is:

create or replace procedure myProcedure (param1 in varchar2 default null, param2 in number default null)

and in SQLServer it is:

CREATE PROCEDURE MyProcName 
@Parameter1 INT = 1,
@Parameter2 VARCHAR (100) = 'StringValue',
@Parameter3 VARCHAR (100) = NULL

Is there a Teradata equivalent? I looked on the web and found nothing.

Answer 1

No, there's no DEFAULT for parameters in SPs and no way to omit a parameter within the CALL statement in Teradata (blame Standard SQL for it).

Only for macros there's both, so a possible workaround might be wrapping the SP CALL in a macro:

REPLACE MACRO myMacro (param1 INT DEFAULT 1
                      ,param2 VARCHAR(100) DEFAULT ''
                      ,param3 VARCHAR(100)
                      ) AS
(CALL MyProcName(:param1, :param2, :param3););

EXEC myMacro(param2 = 'bla');