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.

asked 03 May, 05:47

Sako's gravatar image

Sako
1.2k4661105
accept rate: 23%


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

permanent link

answered 03 May, 07:59

Volker%20Barth's gravatar image

Volker Barth
36.4k343504757
accept rate: 34%

edited 04 May, 07:22

1

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

(03 May, 08:47) Breck Carter

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

Thanks a lot!

(03 May, 08:50) Sako
1

@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?!!

(04 May, 04:27) Sako
Replies hidden
1

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

(04 May, 07:16) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×20

question asked: 03 May, 05:47

question was seen: 131 times

last updated: 04 May, 07:22