Macro default value

Database
Enthusiast

Macro default value

Hello,

I've searched in the documentation and on this forum for an answer to my question prior to this post with no luck. I'm coming from an Oracle background, where answers to questions like this are very simple to find online. Not so much with Teradata, I'm finding! I have a macro with several parameters, and I have specified a few of them with default values. In an Oracle stored procedure, that would allow me to call the procedure without specifying those parameters. In Teradata, they are still required. What's the point of a default value, then?

create macro dropme_dummy(

    important_id integer,

    not_important_ind char( 1 ) default 'Y' )

etc...

exec dropme_dummy( 12 );

EXECUTE Failed. 3816: The positional parameter list has too few values.

exec dropme_dummy( 12, 'N' );

EXECUTE completed.

What's the deal? How can I just create an optional parameter in a macro or stored procedure?

Thanks in advance...

6 REPLIES
Senior Apprentice

Re: Macro default value

You have to call it like this:

exec dropme_dummy(important_id = 12)

Dieter

Enthusiast

Re: Macro default value

Perfect, thank you.

Enthusiast

Re: Macro default value

By the way Dieter, I found your string parsing function and am currently using it, so thanks for that as well.

Tom

Enthusiast

Re: Macro default value

create set table dev_usage_tbl_db.varchar_position( n int not null primary key );

insert into dev_usage_tbl_db.varchar_position

select day_of_calendar

from sys_calendar.calendar

where day_of_calendar <= 64000;


create multiset global temporary table dev_usage_tbl_db.string_set(

    string_text varchar(255 ) )

    on commit preserve rows;


replace macro dev_gh_vwm_db.split_string_to_set(

    string_to_split long varchar,

    string_delimiter char( 1 ) default ',' ) as(

delete dev_usage_tbl_db.string_set;

insert into dev_usage_tbl_db.string_set

select trim( substring( :string_to_split from n for

        ( coalesce( ( nullif( position( :string_delimiter in substring( :string_to_split from n ) ), 0 ) ), 64000 ) ) - 1 ) )

        as string_part

from varchar_position

where n between 1 and char_length( :string_to_split )

    and( substring( :string_to_split from n - 1 for 1 ) = :string_delimiter

        or n = 1 );

-- select * from dev_usage_tbl_db.string_set;

    );

Enthusiast

Re: Macro default value

Can any one please let me know, that can we have default parameter in stored procedure like the above macro?

Enthusiast

Re: Macro default value

Not sure about SP, but if you have to pass the default parameter to SP, you can use macro to pass default parameter and call SP from the macro.