One Star dbh
One Star

Reading cursor returning from Stored Procedure

Hello, 
I'm using Talend Open Studio 5.6.1 and MS SQL server (2000).  It is possible to retrieve and read a cursor returned from a Stored Procedure call?
I've tried using tMSSqlSP without success when calling the sample stored procedure below. I'm trying to get it working first with a simple SP such as the one below. Can anyone give me some tips or point to an example of using tMSSql components (or tMSSqlSP) to call an SP  and then read a cursor from it?
Thanks very much for any help.
Problems 

How to pass in a reference to an "OUTPUT" cursor into the SP?
How to read the cursor that is returned?


CREATE PROCEDURE .
   @myCursor CURSOR VARYING OUTPUT
AS
   SET NOCOUNT ON;
   SET @myCursor = CURSOR
   FORWARD_ONLY STATIC FOR
     SELECT my_id, my_value
     FROM dbo.zExample;
   OPEN @myCursor;
GO
2 REPLIES
Moderator

Re: Reading cursor returning from Stored Procedure

Hi,
Do you want to pass a cursor to a Mysql Procesure? Could you please give us more details about your job requirment?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star dbh
One Star

Re: Reading cursor returning from Stored Procedure

I have a stored procedure in Microsoft SQL Server.  The stored procedure returns a cursor, with two fields. Assessment_id, and battery_id.   The goal is to call the stored procedure, get the cursor, and for each row in the cursor,  split it into the fields it contains.
Here is the final output,  stored procedure, TSQL to call it, and screenshots from Talend Studio
In Talend, I used the tMSSqlSP and tParseRecordSet components.
Output from tLowRow
999|123456
My hovercraft is full of eels|42

Stored procedure
CREATE PROCEDURE .    
AS
    SET NOCOUNT ON;
DECLARE @batteryAssessmentMappingCursor CURSOR --FORWARD_ONLY READ_ONLY FOR
      SELECT assessment_id, battery_id
      FROM dbo.TPDB_AssessmentBatteryMapping;
GO

How to get results out in Microsoft SQL Server Studio
DECLARE	@return_value int
EXEC @return_value = .
SELECT 'Return Value' = @return_value
GO