Hi, We are doing a database query using stored procedures in the handlers. When we do the query on the database we can only fetch the first rown and assign it to variables using ordinal postion and column name. We are not able to access any data from the second row. Please let me know if you need more clarifications. I have also attached a screenshot of the database. We need to get all the data showing in the screenshot and assign it as variables in the handlers. Thanks Karthik[ATTACH]411[/ATTACH]

Originally posted by karthikraja;22803
Hi, We are doing a database query using stored procedures in the handlers. When we do the query on the database we can only fetch the first rown and assign it to variables using ordinal postion and column name. We are not able to access any data from the second row. Please let me know if you need more clarifications. I have also attached a screenshot of the database. We need to get all the data showing in the screenshot and assign it as variables in the handlers. Thanks Karthik[ATTACH]411[/ATTACH]

Unfortunately the Stored Procedure tool has always been limited to a single result. it's something the developers overlooked. Your best bet would be to populate a temporary table with your stored procedure, then query that table with the DB Get Data List toolstep, then clear the table. Not the most elegant, but it works.

Hi Paul, Thank you for the reply. We are planning to move in your way and our database admin also thinking of an efficient solution to overcome this issue. I will keep you posted on the progress. Thanks Karthik

In our Advanced Handlers class I demonstrate how to retrieve multiple records from the database using the Result Set page settings. You have to create the fields for the Result Set through the Utilities|Stored Procedure menu, and give the correct ordinal position and data type of each, but once the bindings are set up you should be able to populate the bound variables just like you do with a DB Query, using a DB Fetch to loop around and get each record result set in turn. I know what the Help says, but I don't think it is correct. Maybe something has changed, but I'm pretty sure I showed this in my last class running IC 3.0 SU10 or 11. Try it and see - if you get one result set, then you should be able to loop through and get the rest (but only via bindings on the Result Set tab, not the Parameters tab - that would just be for inputs to the sproc).

George is correct, I have a bunch of handlers that retrieve multiple rows from stored procedures via the fetch toolstep. When you add a stored procedure to the system you have to add the output definitions to the stored procedure. When you reference the stored procedure in the handler then you should have result set variables you can set and then just use fetch to get the next row.

Hi Mark, I am able to fetch multiple columns using the stored procedures. But I am not able to retrieve multiple rows when the query runs. When you look at the screenshot in my first post you can see the stored procedure returns 3 rows. I can fetch all the columns of the first row. But we are not able to retrieve the columns of all the rows. Anyways we figured a different way to get around that and we are good. I just want to update you what is happening. I appreciate all your help. Thanks Karthik