Avoiding nested cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Avoiding nested cursor

Author

Topic

Masum7
Starting Member

33 Posts

Posted - 2010-05-30 : 21:18:45

Hello everyone
=============================
inbox table
---------
sender status
8801 0
8802 0

ticket table
---------
msisdn ticket
8801 T 12345
8801 T 12346
8802 T 12347

outbox table
-----------
receiver message
8801 your ticket: T123455, T123456
8802 your ticket: T123457

=============
According to above scenario, I need a SP that will read unprocessed rows(status=0) from inbox table and for each unprocessed row from inbox it will build message of outbox rows from the ticket table.

For example for a request from sender 8801 it goes to ticket table and finds rows for 8801 and then put in outbox table that tickets are T123455, T123456.

This can be done by nested cursor like the following:

loop cursor for each unprocessed rows from inbox
loop cursor from tickets for current row of cursor
build message
insert into outbox
======================

Can anyone please suggest me how I can avoid this nested cursor here please.

Masum

Masum7
Starting Member

33 Posts

Posted - 2010-05-31 : 05:02:03

Oh long time, no reply!
Anyway I got the solution. I have eliminated inner cursor loop using row concatenation
Ref: http://stackoverflow.com/questions/194852/in-sql-server-how-do-you-concatenate-many-rows-into-a-single-text-string

Masum