Using stored procedure to enrich customer record

Avatar

Avatar

jamesx29320000

Avatar

jamesx29320000

jamesx29320000

06-09-2017

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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

Vapsy

Employee

Avatar

Vapsy

Employee

Vapsy
Employee

06-09-2017

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

Answers (2)

Answers (2)

Avatar

Avatar

jamesx29320000

Avatar

jamesx29320000

jamesx29320000

13-09-2017

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

Avatar

Avatar

jamesx29320000

Avatar

jamesx29320000

jamesx29320000

11-09-2017

Hi Vipul,

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

Best,

Jim