process each row in table in stored procedure using cursor

Question

My Scenario is bit different. what i am doing in my stored procedure is

Create Temp Table and insert rows it in using "Cursor"

Create Table #_tempRawFeed
    (
    Code            Int Identity,
    RawFeed         VarChar(Max)
    )

Insert Data in temp table using cursor

 Set @GetATM = Cursor Local Forward_Only Static For
    Select DeviceCode,ReceivedOn
    From RawStatusFeed
    Where C1BL=1 AND Processed=0
    Order By ReceivedOn Desc
Open @GetATM
Fetch Next
From @GetATM Into @ATM_ID,@Received_On
While @@FETCH_STATUS = 0
    Begin
        Set @Raw_Feed=@ATM_ID+' '+Convert(VarChar,@Received_On,121)+' '+'002333'+' '+@ATM_ID+' : Bills - Cassette Type 1 - LOW '
        Insert Into #_tempRawFeed(RawFeed) Values(@Raw_Feed)
        Fetch Next
        From @GetATM Into @ATM_ID,@Received_On
    End

Now have to process each row in Temp Table using another Cursor

DECLARE @RawFeed    VarChar(Max)

DECLARE Push_Data CURSOR FORWARD_ONLY LOCAL STATIC
FOR SELECT RawFeed
FROM #_tempRawFeed

OPEN Push_Data
FETCH NEXT FROM Push_Data INTO @RawFeed

WHILE @@FETCH_STATUS = 0
BEGIN
    /* 
    What Should i write here to retrieve each row one at a time ??
    One Row should get stored in Variable..in next iteration previous value should get deleted.
    */
    FETCH NEXT FROM Push_Data INTO @RawFeed 
END
CLOSE Push_Data
DEALLOCATE Push_Data

Drop Table #_tempRawFeed

What Should i write In BEGIN to retrieve each row one at a time ?? One Row should get stored in Variable..in next iteration previous value should get deleted.

What do you mean by "But the Problem is it is not working properly". what is it doing wrong? — Adriaan Stander, Sep 29 '12 at 08:05
Have you checked that there are indeed rows in your `#_tempRawFeed` table before you start iterating? If there are no rows in the table, nothing can be iterated over ... — marc_s, Sep 29 '12 at 08:14
"My scenario is a bit different", different from what? Another question maybe? Then post a link to that question. — Yaroslav, Sep 29 '12 at 08:22
Ok, now edit your question and show how you fill the temp table — Yaroslav, Sep 29 '12 at 08:33
Go thorough this..http://www.sqlfiddle.com/#!3/d41d8/4746 why i am getting only one value ? — , Sep 29 '12 at 08:41

score 0 · Accepted Answer · edited May 23 '17 at 12:18

Regarding your last question, if what you are really intending to do within your last cursor is to concatenate RawFeed column values into one variable, you don't need cursors at all. You can use the following (adapted from your SQL Fiddle code):

CREATE TABLE #_tempRawFeed
(
   Code            Int IDENTITY
   RawFeed         VarChar(MAX)
)

INSERT INTO #_tempRawFeed(RawFeed) VALUES('SAGAR')
INSERT INTO #_tempRawFeed(RawFeed) VALUES('Nikhil')
INSERT INTO #_tempRawFeed(RawFeed) VALUES('Deepali')

DECLARE @RawFeed    VarChar(MAX)

SELECT @RawFeed = COALESCE(@RawFeed + ', ', '') + ISNULL(RawFeed, '') 
FROM #_tempRawFeed

SELECT @RawFeed

DROP TABLE #_tempRawFeed

More on concatenating different row values into a single string here: Concatenate many rows into a single text string?

I am pretty sure that you can avoid using the first cursor as well. Please, avoid using cursors, since the really hurt performance. The same result can be achieved using set based operations.

Want to store each row in variable at each iteration...and delete previous values of variable — , Oct 01 '12 at 08:49

process each row in table in stored procedure using cursor

1 Answers1