@@fetch_status in SQL Server – SQL Server Tutorial

@@fetch_status in SQL Server :-

  • This is one system variable which returns status of the fetch statement  i:e fetch is successful or not.
  • If fetch is successful returns 0, otherwise non-zero value.

Write a program to display all employee names & salaries :-

Declare c1 cursor for

        Select ename,sal from emp

Declare @ename varchar (20)

Declare  @sal smallmoney

 Open c1

Fetch next from c1 into @ename,@sal

       While (@@fetch_status=0)


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

    Fetch next from c1 into @ename,@sal


         Close c1

     Deallocate   c1. 

            Student                                                       Result

Sno     sname     s1   s2   s3                    sno    stot       savg     snes

 1            X         50   60    70

 2            Y         60   75     82

  • Create table student (sno int,sname varchar(20),  S1 int,s2  int  ,s3 int)
  • Create table result (sno int,stot int,savg int,snes  char(4)).
  • Insert into student values(1 ,’A”,50,60,70)
  • Insert into student values (2,’B’,60,52,75)
  • Select * from student.

Program  :

                       Declare c1 cursor

                      For select sno,s1,s2,s3 from student.

                     Declare @sno int,@s1 int ,@s2 int,@s3 int

                      Declare @ stot int,@savg int

                       Declare @snes  char(4).

                 Open c1

                     Fetch next from c1 into @sno,@s1,@s2,@s3

                         While (@@ fetch_status==0)


                       Set @stot=@s1+@s2+@s3

                        Set @savg  =ceiling((@stot)/3)

                           If @s1>35  and @s2>35 and @s3>35

                            Set @snes=’pass’


                             Set @snes =’fail’

                    Insert into result values (@sno,@stot,@savg,@snes)

                     Fetch next from c1 into @sno,@s1,@s2,@s3


                      Close c1

                       Deallocate c1.

Output :-                   sno         stot     savg     snes                 [select * from result]

                                          1             A       55          pass

                                         2              B        60         pass

Leave a Reply

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