Static and Dynamic Cursor in SQL Server :-
Static Cursor in SQL Server with Example :
- If the cursor is declared with static any changes make to the base table changes are not reflected to result-set.
Example :-
- Emp Resultset
- Name sal name sal
- A 5000 A 5000
- ↓ ↓
- Update 5000
Dynamic Cursor in SQL Server with Example :-
- If the cursor is declared with dynamic so any changes make to the base table the changes are automatically reflected to result set.
Example :-
emp resultset
Name sal name sal
A 5000 A 5000
↓ update ↓
6000 6000
Example : program :-
Declare c1 cursor static [(or) dynamic]
For select sal from emp where empno=7788
Declare @sal smallmoney
Open c1
Update emp set sal =5000 (=6000 if dynamic)
Where empno=7788
Fetch next from c1 into @sal
Printt @sal
Close c1
Deallocate c1.
Output :- 4000 (if static)
6000(if dynamic)