@@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)
Begin
Print @ename +’earns’+cast(@sal as varchar)
Fetch next from c1 into @ename,@sal
End
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)
Begin
Set @stot=@s1+@s2+@s3
Set @savg =ceiling((@stot)/3)
If @s1>35 and @s2>35 and @s3>35
Set @snes=’pass’
Else
Set @snes =’fail’
Insert into result values (@sno,@stot,@savg,@snes)
Fetch next from c1 into @sno,@s1,@s2,@s3
End
Close c1
Deallocate c1.
Output :- sno stot savg snes [select * from result]
1 A 55 pass
2 B 60 pass