How to execute MS SQL stored procedure with output parameter using SQL Plugin?

[Enter123]

Hi,

I have vRA 8.9.1 and have been tried to find a way to execute MS SQL Stored procedure with output parameter and use that value on a XaaS form.

I am missing something and not getting either syntax correctly or my logic is completely wrong.

The following code works perfectly fine when executing MS SQL UDF and getting back value:

var query = "DECLARE _value int; EXEC _value = [dbo].[UDF_Fetch_VALUES]'" + parameter1+ "','"+parameter2+"',"+parameter3+"; SELECT 'Return value' = _value";

var rowsAffected = db.readCustomQuery(query);

var receievedValue;

for each(item in rowsAffected){
receievedValue=item.getProperty("Return value");

}
return receievedValue

I am not able to do similar with SP.

My SP looks like this and of course it works perfectly if you execute it SQL Management studio:

DECLARE _value int

EXEC _value = [dbo].[DB_Instance_available_values]
_db_lc = N'DB_INSTANCE_name',
_value_Count = number_value

SELECT 'Return Value' = _value

I tried to use something I found online, like:

Call Database Stored Procedures via JDBC (vcoportal.de)

or

Executing Stored Procedures with Output Parameters through vRO | Cloud Brokers

But I don't find a way how to handle those parameters.

If someone has a working piece of code or has some tip how to do it properly, would be appreciated.

Thanks

[eoinbyrne]

Hi

I'm not clear on whether your procedure takes parameters? If so, the thing you need to be aware of is that in the line

var ps = con.prepareCall("{call nameOfStoredProcedure(?,?)}");

Each of the '?' characters corresponds to a parameter to your procedure. Then, in each parameter case you use

ps.setString(position, value)

[Enter123]

Hi,

it has 2 input parameters:

@inst_db_lc = N'DB_INSTANCE_name',
@Des_value_Count = number_value

and DB guys have created this to capture output value:

DECLARE @return_value int

SELECT 'Return Value' = @return_value

How to capture that value?

[eoinbyrne]

Looking at the code from vcoportal again

The section in red details the handling of the ResultSet which is returned by the execution of the call to the procedure. You can see the documentation for ResultSet here (from vroapi.com)

Anyway, since your procedure returns ONE value, you can access it by doing this

var resultSet = callStm.executeQuery();
var procedureReturnValue = null;
if(rs.next()) // if the result set has content
{
    procedureReturnValue = rs.getString(1); // get the result at position 1 in the first row
}
rs.close();
callStm.close();

Note, you should use all of the try/catch logic as depicted in the example, just fit your result set handling inside where the comment says "Process the results...."

The other posted example will also work so you have your choice

-HTH

[Enter123]

Thanks for the response! Luckily managed to make it work after SP was modified by DB team.

[Jeff_CH]

Hi

Maybe this will help you in the right direction, this works for me:

var sqlQuery = "EXEC <<stored procedure name>> '" + <<some input value>> + "'";
var receivedValues = serverDatabase.readCustomQuery(SQLQuery);

// Loop through output from stored procedure
for each ( receivedValue in receivedValues ){
System.log("receivedValue : " + receivedValue);
// Returns something like this:
// DynamicWrapper (Instance) : [SQLActiveRecord]-[class com.vmware.o11n.plugin.database.ActiveRecord] -- VALUE : ActiveRecord: {COUNT=1}
// So in my case the record name is 'COUNT'
System.log("output : " + receivedValue.getProperty("COUNT"));
};

[Enter123]

Hi,

DB team had to modify SP and declare output parameter properly.

Now this code works:

var cstmt = con.prepareCall("{call dbo.DB_Instance_available_value_check(?,?,?)}");
cstmt.setString(1,parameter1);
cstmt.setString(2, parameter2);

//3 - index of the output parameter, 12 = varchar

cstmt.registerOutParameter(3, 12);

cstmt.execute();
var output = cstmt.getString(3);
return output;

I hope this will help someone in the future.