Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Using stored procedure to enrich customer record

jamesx29320000
Level 2
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
Vapsy
Correct answer by
Employee
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
Vapsy
Correct answer by
Employee
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

jamesx29320000
Level 2
Level 2

Hi Vipul,

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

Best,

Jim

jamesx29320000
Level 2
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