Call stored procedure from Adobe Campaign Classic

One member of our team working on a different project asked me if there is a way to call Stored Procedure from Adobe Campaign Classic workflow. He was looking to get an option to receive an output parameter also. So I thought of trying a POC and here is the outcome. In my local instance I had Sql Server and thus thought about testing few other sql server specific features
Step 1: Create a basic stored procedure

ALTER PROCEDURE USP_GetRecipient
    -- Add the parameters for the stored procedure here
    @ageGreaterThan int = 0,
    @state Varchar(20) = '',
    @totalRows int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
  
    SELECT @totalRows = Count(1) from NmsRecipient r;

    SELECT iRecipientId,sEmail from NmsRecipient r where [dbo].[YearsDiff](GETDATE(),r.tsBirth) > @ageGreaterThan and sStateCode = @state

    Select top 10 iOfferId,sName from NmsOffer where iOfferId > 0

END 

The Procedure  selects recipients filtered by age and state code. I tried to keep an integer and string input variable. So picked age and state
Also it returns the no of rows as an OutPut parameter.
In addition, SQL server SP allows you to return more than one result set. So I added the top 10 offfer related sql also into the stored proc. If run using SQL Server Management Studio, it will share you two result sets/tables as you know

Step 2: Create a workflow to test the proc
Created a basic workflow with a start activity and javascript activity. I initially thought if I could use "SQL Code" activity to achive something similar. But figured out this activity only suits DML/DDL. But no select kind of operation.
So stuck to my original plan of using the JavaScript activity.

Here is the code I wrote in the javascript activity. The SQL I ran was specific to SQL Server when you need to run a stored proc. Other databases does have similar option, but I don't have access at this point.

var sql = "DECLARE  @return_value int,"
sql+="    @totalRows int "

sql += "EXEC  @return_value = [dbo].[USP_GetRecipient] "
sql += "    @ageGreaterThan = 10, "
sql += "    @state = N'WB', "
sql += "    @totalRows = @totalRows OUTPUT";
sql += "    SELECT  @totalRows as TotalRows";
sql += "    SELECT @return_value as ReturnValue";

var res= sqlSelect("rcp,@recipientId:int,@email:string",sql);

logInfo("Type:" + typeof(res) + "\n" + "Result:" + res.toXMLString());

for(var index in res.rcp)
{
  logInfo("Type inside for:" + typeof(index) + " - " + index);
 
  var rcp = res.rcp[index];
  logInfo("Type of Rcp:" + typeof(rcp) + " - " + rcp.toXMLString());
  logInfo("Recipient Id:" + rcp.@recipientId + " - Email:" + rcp.@email); 
}

Result:
I was able to get the result from the Recipient schema, but not the Offer schema. So Adobe doesn't allow multiple results sets from Database. It only provides access to the first set.
Also, it is not possible to get the output parameters or the return values. As you can see in the last 2 lines of the SQL, I was selecting the variables separately to see if that is an option. But as you already expected, because I can't access 2nd ResultSet, how will I access the variables in this fashion.
One workaround is, selecting the variable as part of the First Select statement. That way the value will be repeated for every rows, and you will be able to read it. Not a good option from performance point of view, but an option :) (example: Select @totalRows as TotalRows, iReccipientId, sEmail from nmsRecipient ...)

Additional Findings:
The Adobe documentation in the HTML help file for sqlSelect is very basic, like many other functions. So thought about documenting my findings
var res= sqlSelect("rcp,@recipientId:int,@email:string",sql);
Will return XML object as the Adobe doc says and you can run functions like toXMLString() without any problem.
If you need to loop through the records returned by the Select, here is the option
for(var index in res.rcp)
{
  logInfo("Type inside for:" + typeof(index) + " - " + index); //Type is String
 
  var rcp = res.rcp[index];
  logInfo("Type of Rcp:" + typeof(rcp) + " - " + rcp.toXMLString()); //Type is XML Again
  logInfo("Recipient Id:" + rcp.@recipientId + " - Email:" + rcp.@email); 
}

The variable I used as index, is something that I was expecting to be XML as well. But to my surprise it was of type string (not even integer) representing the index of the child record.
The DOMDocument functions like getElementsByTagName, doesn't work on XML result. Probably you have to convert before you run those functions.

Here is the log
 
Following lines of code generate log via below codes to test XMLDocument on the result

var res= sqlSelect("rcp,@recipientId:int,@email:string",sql);
var xmlDom = DOMDocument.fromXMLString(res);
var nodes = xmlDom.getElementsByTagName("rcp");
var node = nodes[0]
logInfo("Type of nodes:" + typeof(nodes) + ", Value of type inside array" + typeof(nodes[0]) + ", Type of node:" + typeof(node));
logInfo("Lenght of nodes:" + nodes.length);
logInfo("Value nodes[0]:" + nodes[0].toXMLString());