Order of passing parameters into a procedure

[Baron]

I have a procedure with about 20 flags as input parameters and each time I call the procedure with setting one of those flags so the procedure looks something like this:

create or replace procedure myproc(

f1 bit default 0,

f2 bit default 0,

f3 bit default 0,

f4 bit default 0

)

begin

select f1, f2, f3, f4;

end;

Now, whenever I want to call the procedure myproc I should pass all the parameters preceding the parameter which I want to set.

As example, if I want to set the flag f4 then the only way is to call the procedure like this:

call myproc(0,0,0,1);

My question is, is there a way to avoid the need of passing all the unneeded parameters?

I mean something like call myproc(f4=1);

The reason to my question was, because my flags list is more than 20 flags, and then it is inconvenient to call the procedure.

[VolkerBarth · Accepted solution]

Yes, you can specify arguments by position or by name, just as you have suggested, see here and here.

Just to add: When specifying arguments via position, and you want the default value to be used, you can use the "default" keyword for that (i.e. you do not need to repeat the actual default value), such as

call myproc(default, default, default, 1);

One further note: As the docs tell, named parameters are only supported when using CALL or as @Sako has reported, when using SELECT ... FROM "procedure-call". They are not supported when using SELECT "function-call".

[Breck_Carter · Accepted solution]

> you can use the "default" keyword

Well done! (that tidbit is well hidden, down inside the Help topic rather than where it should be: the Syntax section)

[Baron · Accepted solution]

Ah, it is already possible, but I didnt try it.

Thanks a lot!

[Baron · Accepted solution]

@volker barth, in contrast to what you lately written, named parameters are also supported when selecting from a procedure:

In my example, I can even say: select * from myproc(f4=1)

Or maybe I didn't get your last point correctly?!!

[VolkerBarth · Accepted solution]

Ah, I checked with SELECT <function-call>, and that does fail with a syntax error as expected, but it seems to work for SELECT from <proc-call>. Thanks for the pointer, and I'll adapt my answer. 🙂