It doesn't seem like I'm having problems executing my cursors that are enclosed in a stored procedure. But I want to find a more effective way to achieve the same.
Here it is.
Stored Procedure: RawFeed.sql(performed every 5 minutes)
Set @GetATM = Cursor For
Select DeviceCode,ReceivedOn
From RawStatusFeed
Where CRWR=2 AND Processed=0
Order By ReceivedOn Desc
Open @GetATM
Fetch Next
From @GetATM Into @ATM,@ReceivedOn
While @@FETCH_STATUS = 0
Begin
Set @RawFeed=@ATM+' '+Convert(VarChar,@ReceivedOn,121)+' '+'002307'+' '+@ATM+' : Card Reader/Writer - FAULTY '
Exec usp_pushRawDataAndProcess 1,@RawFeed
Fetch Next
From @GetATM Into @ATM,@ReceivedOn
End
Set @GetATM = Cursor For
Select DeviceCode,ReceivedOn
From RawStatusFeed
Where CRWR=0 AND Processed=0
Order By ReceivedOn Desc
Open @GetATM
Fetch Next
From @GetATM Into @ATM,@ReceivedOn
While @@FETCH_STATUS = 0
Begin
Set @RawFeed=@ATM+' '+Convert(Varchar,@ReceivedOn,121)+' '+'002222'+' '+@ATM+' : Card Reader/Writer - OK '
Exec usp_pushRawDataAndProcess 1,@RawFeed
Fetch Next
From @GetATM Into @ATM,@ReceivedOn
End
Similarly, I have 10 more statements SETthat differ in the condition parameter WHEREand the string enclosed in the variable @RawFeed.
For each line that I get, I execute another stored procedure on that particular line.
My question
Is there a better way to achieve this without using cursors?
@RawFeed string, usp_pushRawDataAndProcess. , , INSERT,UPDATE,DELETE .
1 STRING IN usp_pushRawDataAndProcess
NMAAO226 2012-09-22 16:10:06.123 002073 NMAAO226 : Journal Printer - OK
WMUAO485 2012-09-22 16:10:06.123 002222 WMUAO485 : Card Reader/Writer - OK
user1194919