Using stored procedure to enrich customer record

[jamesx29320000]

Hi all,

I'm trying to figure out if there is a way to call a stored procedure by customer and store the output of the stored procedure into the workflow temp table and assign it as a targetData.

For example, we have a stored procedure on our sql server called GetCustomerName, with an input of the customerId. The output is the customerName. I have the code so far below, but I'm not sure how to proceed further, any help would be greatly appreciated!

  var customers = xtk.queryDef.create(

    <queryDef schema={vars.targetSchema} operation="select">

      <select>

        <node expr="@id" groupBy="1" />

      </select>

    </queryDef>

  ).ExecuteQuery();

     for each (var customer in customers){

     sqlExec(EXEC GetCustomerName @customerId);

     };

Best

Jim Xue

[Vipul_Raghav · Accepted solution]

Hi Jim,

This is bad design.

If your queryDef results in 500 customer records, you will be firing the stored procedure 500 times.

Imagine if you are working with million customers.

I think you are planning to run a user defined function and not stored procedure.

Can you not achieve your requirement using the workflow activities.

You can query on your target schema, use an enrichment to link it with customer and pull the customer name

Regards,
Vipul

[jamesx29320000]

Hi Vipul,

Thank you very much for the confirmation. We are just exploring different approaches to resolve a business ask.

Best,

Jim

[jamesx29320000]

Hi Vipul,

Just for curiosity sake, how can you execute a store procedure in a workflow and pass the output of that stored procedure down through the workflow as data to be leveraged?

many thanks,

Jim