Local and Global Cursors – SQL Server Tutorial

Local and Global Cursors  :-

  • If cursor is local then the cursor can access with in the program only.
  • If cursor is global then the cursor can be declared in one progress can be used in another program.

Global is default

Program (I) :-

             Declare c1 cursor global

                      For select ename,sal,comm from emp.

               Declare  @ename varchar(20)

                Declare @sal smallmoney  @comm smallmoney.

                Declare @totsal smallmoney.

                            Open c1

                           Fetch next from c1 into @ename,@sal,@comm

                            While (@@fetch_status=0)


                              Set @totsal=@sal+isnull(@comm,0)

                              Print  @ename+’earns’+cast(@total as varchar)

                         Fetch next from c1 into @ename,@sal,@comm


                             Close c1.

Note  :-

Program(II)  :-  Calculating   annual salary using cursor c1:

                                        Declare  @ename varchar(20)

                                         Declare   @sal smallmoney,@comm smallmoney

                                         Declare @totsal smallmoney

                                              Opn c1

                                      Fetch next from c1 into @ename,@sal,@comm

                                       While (@@fetch_status=0)


                                          Set @totsal=(@sal+isnull(@comm,0))*12

                                          Print  @ename+’earns’+cast(@totsal as varchar)

                                        Fetch next from c1 into @ename,@sal,@comm


                                         Deallocate c1.

                            Order_details                                     items

      Ordno         icode             qty                     icode         descrpt        price

          1000           1                   10                        1                                  100

         1000            2                   30                         2                                  150

          1000           3                   15                         3                                  50

          1001           1                 20

To display like :-   

              Item code             dept                 rate            qty             value

                   1                         —                   100            10               1000

                   2                         —                    150            20              3000

                   3                         —                    50              15               750

Total bill=4750

Program :-

                        Declare   c1   cursor

                            For select    icode,descno,rate,qty

                               From   order-details,items

                      Where   order_details icode=items.icode

                                     And      andno=1000.

                      Declare @icode int,@descrip varchar(20)

                      Declare   @rate   int,@qty int

                         Declare @value int

                        Declare   @tbil int.

                       Set @tbil=0

                    Open c1

    Fetch  next from c1 into @icode,@descrip,@rate,@qty

                    While (@@fetch_status=0)


                   Set @value=@qty*@rate

       Set   @tbill=@tbill+@value

          Print  cast (@icode as varchar)+’ ’+@descrip+

                 ‘ ‘+cast(@rate as varchar) +’ ‘+cast+@qty

                          As varchar)+cast(@value as varchar)

                   Fetch next from c1 into @icode,@descrip @rate@qty.


                       Print ‘total bill :-‘+cast(@tbill as varchar)

                        Close c1

                     Deallocate c1.

Leave a Reply

Your email address will not be published. Required fields are marked *