CODEWITHSUNDEEP
biztalk
TOMMY WANG
TOMMY WANG

Reputation: 1402

BizTalk SQL Adapter composite operation, chaining stored procedure calls

We need to call three stored procedures on the same database, thinking to use composite operation to wrap them in the same call of the same transaction.

Question is, we need the result of first stored procedure to be used as the input for the 2nd and 3rd procedure, is this doable?

Thanks

Upvotes: 0

Views: 316

Answers (3)

DTRT
DTRT

Reputation: 11040

Yes, you absolutely can to that, but you would not use a Composite Operation.

You would use an Orchestration that performs the calls in sequence, using the Response of one to create the Request of the next using a Map.

This is actually a very common pattern.

Upvotes: 0

zurebe-pieter
zurebe-pieter

Reputation: 3266

This is not possible I'm afraid.

The input of your composite operation is an XML instance, where every input parameter is supplied before hand.

If its really necessary to execute these particular stored procedures, you can try wrapping them into one, custom stored procedure, where you are free to to what you want.

One could also try merging the logic from these 3 stored procedures into one new one. Try to think about scalar functions, table types, table valued functions and so on. SQL server has quite the arsenal to let you do what you want.

Upvotes: 1

Gruff
Gruff

Reputation: 555

No, unfortunately not. The map will run and create the XML that the SQL adapter will use to execute afterwards.

You could look at making a two-way send port that only runs the first stored procedure; and another send port that subscribes to the response of the first send port and runs the second and third procedures.

Upvotes: 1

Related Questions