VMware Cloud Community
Enter123
Enthusiast
Enthusiast

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

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 @return_value int; EXEC @return_value = [dbo].[UDF_Fetch_VALUES]'" + parameter1+ "','"+parameter2+"',"+parameter3+"; SELECT 'Return value' = @return_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 @return_value int

EXEC @return_value = [dbo].[DB_Instance_available_values]
@inst_db_lc = N'DB_INSTANCE_name',
@Des_value_Count = number_value

SELECT 'Return Value' = @return_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

Labels (2)
Reply
0 Kudos
6 Replies
eoinbyrne
Expert
Expert

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)

Reply
0 Kudos
Enter123
Enthusiast
Enthusiast

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?

Reply
0 Kudos
Jeff_CH
Enthusiast
Enthusiast

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"));
};

eoinbyrne
Expert
Expert

Looking at the code from vcoportal again

eoinbyrne_1-1669888003477.png

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)

eoinbyrne_2-1669888122405.png

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

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.

Reply
0 Kudos
Enter123
Enthusiast
Enthusiast

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

Reply
0 Kudos