Calling a stored procedure with optional parameters

Help
1

Hello,

Question about optional parameters (in stored procs but maybe it applies to other programmability too?)

For some stored procedure with a signature like

CREATE PROCEDURE myproc(a int DEFAULT 1, b int DEFAULT 2, c int DEFAULT 3, d int DEFAULT 4) ...

How can I call it without passing, say, c?

2

Welcome aboard id2431! :wave:

We’re happy to help!
Are you on managed or self-hosted service and what version number?

3

Thanks. This instance is managed

4

Since you’re on the managed service, just curious, have you contacted support yet?

:rescue_worker_helmet: If not, they’ll create a support ticket to assist you with any managed service issues.

In the meantime, maybe someone here in the community has dealt with this issue and con offer more details.

Either way, we hope it’s figured out quickly and would love to hear an update how this gets resolved. :slightly_smiling_face:

5

Thank you, I will try support if no one here has an idea. It seemed like the kind of knowledge-base question not answered by the documentation that others might help with (or read later).

6

Exactly! :tada: Another reason why posts and feedback from members are so important. We’re able to add important details into the documentation and help others save time and energy. Can’t wait to hear updates from you soon!

7

Hello,
If you use default values for your parameters and you want to pass value to d without passing value to c for example , you have to call your stored procedure using NULL value.
In this example it should look like :
CALL myproc(1,2,NULL,4);

Hope it helps !

1 Like
8

Thanks, I had tried this before, but it appears to set the given parameter’s value (here, c) to null, not to its default, which is what I’m looking for.

DELIMITER //

CREATE OR REPLACE PROCEDURE myproc(a int DEFAULT 1, b int DEFAULT 2, c int DEFAULT 3, d int DEFAULT 4)
RETURNS QUERY (a int, b int, c int, d int)
AS
DECLARE q QUERY (a int, b int, c int, d int);
BEGIN
	
	q = SELECT a, b, c, d;

	RETURN q;

END //

DELIMITER ;

Expecting c = 3 in the results…

ECHO myproc(5, 6, null, 8);
9

Hi,
Apologies for my misinformation, I thought it was possible.

I would recommend to do like a MySQL procedure where you check if a certain parameter is NULL and replace or change instruction block depending of the parameter value.

I contacted our engineering team, “named parameter” is a feature that is requested and discussed. If this is a high priority for your application, I can add some priority to this feature request.

Thanks for your feedback !

10

Thanks, this is what I meant. I wouldn’t call it high priority, more of a nice-to-have for data analysis, but since the majority of the interaction with the data is via API or UI, SPs are not critical to end users, “only” devs ;).

1 Like