ZURINET
asked on
CURSOR FOR versus While loop
Hi all
I have the table below MyTable
Given that I need to select the first row using <<select Top 1 from my table>>
How can I process the whole rows with 1 transaction.?
That is, select P_Id 1 row (Do some processing )
when done with processign P_Id 1 row
The select P_Id 2 row (Do some Processing ) and so on..
I need to accomplished this with a single storeprocedure transaction..
an Example code or tutorial will be nice
Thanks in Advance
I have the table below MyTable
Given that I need to select the first row using <<select Top 1 from my table>>
How can I process the whole rows with 1 transaction.?
That is, select P_Id 1 row (Do some processing )
when done with processign P_Id 1 row
The select P_Id 2 row (Do some Processing ) and so on..
I need to accomplished this with a single storeprocedure transaction..
an Example code or tutorial will be nice
Thanks in Advance
P_Id LastName Address validFrom Valid until status
1 Trinity 26Broad 01.05.2010 29.06.2010 1
2 Mike 20Broad 01.01.2011 29.02.2011 1
3 James 11Broad 01.03.2011 29.04.2011 0
4 Phili 13Broad 01.05.2011 29.06.2011 0
5 Obama 16Broad 01.07.2011 29.08.2011 1
6 Clinton 11Broad 01.03.2010 29.04.2010 0
7 Janet 26Broad 01.01.2010 29.02.2010 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is much better to use set based operations if you can rather than itterative methods. Perhaphs if you tell us what processing you need to do per row we can help you with a set based operation to do it across all rows.
Cursor is slow and consume memory than while loop . so it's good to use while loop.
ASKER
Hi ShogunWade
Thanks for the feeback..
I just need to extract the value in each row and store it in another table..
i.e in Java
for (row i = 0; i < table.row; i++) {
insert into mynewTable = table[row1];
}
Thanks in Advance
Thanks for the feeback..
I just need to extract the value in each row and store it in another table..
i.e in Java
for (row i = 0; i < table.row; i++) {
insert into mynewTable = table[row1];
}
Thanks in Advance
Please refer the below links
http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741
http://weblogs.asp.net/jgalloway/archive/2006/04/12/442618.aspx