Call Stored Procedure and get resultset - Update

Suppose we have Stored Proc in SQL Server that produces a resultset (bunch of output records) and you want to then use this resultset further in the process mapping such as mapping it to another target etc.

Here is an example of the storec proc that exists in your database:

create procedure GetProducts
@product varchar(10)
AS
SELECT Products, Templates, Characteristics
From dbo.Limits
WHERE Products = @product

Now in the attached custom plugin (attached below in text file) you can pass the product value as a parameter (plugin uses the hard coded value for now but explains how to get context, refer to its comments).

You need to edit the DBInfo in the variable "dbConnectionInfoId" by going to the DB Info activity (Services > Connector > Database Info) and clicking on the activity name and copying the entity id from the pop up window and pasting it in the plugin. Refer to the comments in the plugin code.

Once the custom plugin is executed in the flow, the result set is in XML format output. Go to its process flow logs repository and save the output XML locally.

call_sp2_8106.jpg

 

call_sp3_9377.jpg

 

call_sp4_1895.jpg


In order to use the output XML further in the flow you can create an XML Schema of this file and place Mapping activity after this Custom Plugin activity to convert this resultset data into any other format.

Please refer to below attachments.

capture_sp_9890.jpg

Have more questions? Submit a request

0 Comments

Article is closed for comments.