Hi All....
While Using Cursor I noticed this For a Dynamic Cursor:
Table Desc
Create Table #Temp(ID int,Data Nvarchar(40))
Insert into #Temp
Select 1,'One' Union all
Select 3,'Three' Union all
Select 4,'Four' Union all
Select 5,'Five' Union all
Select 6,'Six' Union all
Select 7,'Seven' Union all
Select 8,'Eight' Union all
Select 9,'Nine' Union all
Select 10,'Ten'
The Cursor Code:
Set @CurTest = Cursor Dynamic
For
Select ID,Data From #Temp
Open @CurTest
Fetch From @CurTest into @Id,@Data
While @@FETCH_STATUS=0
Begin
if @Id=1
Begin
Print 'ID Data'
Print'-- ------'
Update #Temp set ID=2 Where ID=6
Insert into #Temp Values(2,'Two')
End
Print cast(@Id as Nvarchar) +' '+@Data
Fetch Next From @CurTest into @Id,@Data
End
The Output is:
ID Data
-- ------
1 One
3 Three
4 Four
5 Five
2 Six
7 Seven
8 Eight
9 Nine
10 Ten
2 Two
In this output i am able to see the inserted Data and Updated Data.... But in the Cursor Statement if i Use
Set @CurTest = Cursor Dynamic
For
Select ID,Data From #Temp Order by ID,Data
Then the output i am Getting is:
ID Data
-- ------
1 One
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
This Output i am not unable to understand.. if i use Order by Clause then i am not able to see the Updated rows or Inserted Rows.. i Read that Dynamic Cursor iterates over the original real data. All changes are seen by the cursor without any special handling of the changes..
Any Explanation Will be Helpful...