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)