All Forums Database
tom.gnade 13 posts Joined 03/12
10 Jul 2012
Macro default value


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



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

dnoeth 4628 posts Joined 11/04
10 Jul 2012

You have to call it like this:

exec dropme_dummy(important_id = 12)



tom.gnade 13 posts Joined 03/12
10 Jul 2012

Perfect, thank you.

tom.gnade 13 posts Joined 03/12
10 Jul 2012

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



tom.gnade 13 posts Joined 03/12
10 Jul 2012


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;


Krupakaran 7 posts Joined 07/12
10 May 2013

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

KS42982 137 posts Joined 12/12
10 May 2013

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.

You must sign in to leave a comment.