SQL Server Stored Proc OUTPUT Parameter Not Recognized

How To
1

I want to execute a stored procedure and return a value through an OUTPUT parameter.
Per the documentation it seems it should be supported: “Make loads interface of input/output parameters”. The output parm is recognized as a data element downstream, but it always returns null.

Any suggestions on what I could try to get this stored proc output parameter to return to the scenario?

OUTPUT parm not recognized
OUTPUT parm not recognized1920×1088 106 KB

2

@OmaCoder :raised_hands:

Hi there! I’m an AI that is still learning. It looks like you’re having trouble getting an output parameter to return a value from a stored procedure. Could you please provide a bit more detail about the issue you’re having? That way, our community members can better assist you.

All of this helps us to get a deeper understanding of the challenge you face. :make:

3

Create a support ticket.

4

Hey @OmaCoder , I am no expert in this but maybe we can get you there together.
It seems like you are assigning a default value of 0 in your emailID variable, which it then also seems to return in the “Return” object.
What if you remove this default value? Does it come up in the Make module so you can set it?

@ManishMandot Your spam in multiple tickets with this message is not helping…

1 Like
5

When I try without the default value in the parameter definition of the stored procedure, the execution fails due to the EmailID parameter missing.

I am not using the Return clause. The reason you see it in Output Bundle 1 is because sql server always returns 0 (unless there is an error in the stored proc), unless otherwise specified with the RETURN clause in the stored proc.

6

@OmaCoder It’s been a while - did you get this resolved? I’m facing the same issue.

7

Sadly, no!! So strange thst such a common property isn’t working. I’m happy that it wasn’t just me!!

I was able to work around it by using the RETURN clause. (I only needed to return 1 value so I just threw it in the RETURN and moved on).

If you happen to figure out how to utilize the OUTPUT please share!! :grin:

8

Yep. I’m also having issues because my database is running different collation than the master database. (A known issue in Make + Azure DB) Because of this, I can’t access tables at all, I have to run everything via stored procedures :frowning:

I’ve been going around the collation and sp output issue by returning json from the sp and parsing it in Make. I hate it, but you gotta work with what you have :frowning:

9

The support team has confirmed this as a bug with the module. It may be fixed in the future.

Earlier, I mentioned that I’ve been returning JSON from my stored procedure (sp) as a workaround. For those experiencing similar issues, note that output parameters in the stored procedure can also interfere with returning a Recordset to the module.

Here’s my workaround for the issue:

  1. If the stored procedure has both output parameters AND an output recordset, the contents of the recordset won’t be visible in the output bundle, making them unselectable. However, you can manually provide them to subsequent modules, for instance, using {{1.recordset[0].OutputResults}}…
  2. To make the recordset visible, hide the output parameters in the stored procedure, REMOVE the SQL module completely, and then re-add the module using the same stored procedure. After running it once, the recordset will appear. Subsequently, you can reintroduce the output parameters in the stored procedure, and the module will recall the recordset for future runs.