CODEWITHSUNDEEP
sqlteradata
Brad
Brad

Reputation: 49

Add Optional Parameters to Teradata Stored Procedure

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.

Upvotes: 1

Views: 3668

Answers (1)

dnoeth
dnoeth

Reputation: 60462

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');

Upvotes: 3

Related Questions