Expand my Community achievements bar.

SOLVED

Using stored procedure to enrich customer record

Avatar

Level 2

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

1 Accepted Solution

Avatar

Correct answer by
Employee

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

View solution in original post

3 Replies

Avatar

Correct answer by
Employee

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

Avatar

Level 2

Hi Vipul,

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

Best,

Jim

Avatar

Level 2

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