An alternative to using cursors in a SQL Server stored procedure

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 
+5
3

SQL Server, , , .

, .

RawStatusFeed, proprietry, , . , , .

, , SP, , . , , . , . .

, , RawStatusFeed usp_pushRawDataAndProcess. , , DeviceCode .


, , , , , usp_pushRawDataAndProcess .

usp_pushRawDataAndProcess SP .


usp_pushRawDataAndProcess , , .

, , , . .

SQL Server 2005 , CLR usp_pushRawDataAndProcess.

, , - .

+3

- . , , .

-, , .

, . -, , , , Dynamic Optimistic, , , , , , , .

. :

: I

: II

: III

, , , , , , . -, , .

, , - , .

SQL Server . , , , , , SQL Server , : Merge Join, Nested Loop Join Hash Join. SQL Server , , . , , , .

. .

+3

Exception for CURSOR on SQL Server

1.While loop
2.Recursive CTE
0
source

All Articles