Many a times, we come across a Scenario where we need to deal with Stored Procedure (further referred to as SP). SP can be further classified into 2 types:
1- SP having only a single Select statement returning all the columns required for one/many database tables.
Example: select Name, City, IBU from db_details
2- SP having a separate Select statement to return each of the columns from one/many database tables.
Example: Select Name from db_details where city=’Delhi’;
Select IBU from db_employee where Experience>3;
In Case1- This SP will return only one Resultset corresponding to the single Select statement, with single/multiple rows in it.
In Case2-This SP returns 2 Resultset corresponding to each of the individual Select statement,
each Resultset consisting of one/many rows.
In the following blog, I have described the format of the Data Type that has to be used to get the two respective Resultset in the JDBC response. The rest of the process is similar to other JDBC scenario.
We will be passing Location and IBU of people in the source. In our target file, we want Name and DOB. The logic of each is as follows:
1- Name- All the Names in the DB where city = location
2- DOB- Date of birth of persons whose city=location and IBU=IBU(passed in the source file).
This is the required Stored Procedure (MsSQL)-
CREATE PROCEDURE [dbo].[SP_EMPLOYEE1]
— Add the parameters for the stored procedure here
@P_City Char(50), @P_Dept Char(50)
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
SELECT Name from Employee where city = @P_City;
SELECT DOB from Employee where city = @P_City And IBU=@P_Dept;
Source DT Structure:
JDBC request DT structure:
Here SP_EMPLOYEE1 is the name of the stored procedure. We are passing location as P_CITY and IBU as P_DEPT. While mapping, map Table with ‘SP_EMPLOYEE1’(the name of your SP) , ACTION to EXECUTE, TYPE as ‘CHAR’.
JDBC response DT structure:
Please follow the above format. You have to keep it as ‘STATEMENT_response’ , ‘response_1’ (for 1st select statement),‘response_2’ (for 2nd select statement) and ‘row’.
From 1st select statement, we get Name.
From 2nd we get DOB(date of birth).
JDBC target DT structure:
In the target structure, the fields Company,IBU and location can be mapped to constants. response_1 should be mapped to Records1 and response_2 to Records2.
For the rest of the IR and ID objects please refer the following link: