cursors in Sybase

Hello All,

I need some help regarding the implementation of nested cursers in sybase. I have created a stored procedure below to do the below task. The issue is that I am getting the same string inserted for all the PDBC_PFX in the target table.
I have tried to use SET @lResult='' in the inner cursor and then its inserting all the blank values into the target table. Please advice.
 
Source table : CMC_BSTX_SUM_TEXT
PDBC_PFX   SEQ_NO       BSTX_TEXT                                                              
--------         -----------         ---------                                                              
1579              0                   BCN5, CO20, ER75
1579              1                   FP5, WC6, MATW, OPRH
1579              2                   T
1600              0                   TW, FP5, AS5, MHSA15, DME20
1600              1                   ER100, UR40
 
Expected output in Target table  : CMC_BSTX_SUM_TEXT_TEMP (concatenate multiple rows into a single row in the ascending order of SEQ_NO)

PDBC_PFX        BSTX_TEXT
-------------        -----------------------
1579                  BCN5, CO20, ER75FP5, WC6, MATW, OPRHT
1600                  TW, FP5, AS5, MHSA15, DME20ER100, UR40

************************************************************************************************************
CREATE PROC test11
AS
BEGIN
 DECLARE @CODE1 char(4),
                  @lResult char(255),
                  @lText char(255)
 DECLARE c1 CURSOR
      FOR
      SELECT distinct PDBC_PFX  FROM CMC_BSTX_SUM_TEXT  
      COMMIT
      OPEN  c1
      FETCH c1 INTO @CODE1

  While (@@SQLSTATUS !=2)
  BEGIN
                  DECLARE curmovedown CURSOR
                  FOR
                        SELECT TEXT
                    FROM CMC_BSTX_SUM_TEXT where PDBC_PFX =@CODE1  order by SEQ_NO
        COMMIT
        OPEN  curmovedown
        FETCH curmovedown INTO  @lText
                               While (@@SQLSTATUS !=2)
                               BEGIN
                               SET @lResult= @lResult+@lText                  
                               FETCH curmovedown INTO @lText
                               END
       insert into CMC_BSTX_SUM_TEXT_TEMP values (@CODE1,@lResult)        
       COMMIT
       CLOSE curmovedown
       DEALLOCATE CURSOR curmovedown
FETCH c1 INTO @CODE1
 END
CLOSE c1
DEALLOCATE CURSOR c1
END  
*********************************************************************************************************************
Start Free Trial