Correct the Cursor program

Posted by mareesnectar-12158 under Sql Server on 12/20/2011 | Points: 10 | Views : 2290 | Status : [Member] | Replies : 20

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

CREATE TABLE [dbo].[emp](
[sno] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Company_name] [varchar](50) NULL,
[salary] [int] NULL,
CONSTRAINT [PK__emp__DDDF644603F0984C] PRIMARY KEY CLUSTERED
(
[sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

declare mycur
cursor
for
select
name,salary
from
emp
open mycur
declare @name varchar(50)
declare @salary int
fetch next from Mycur into @name,@salary
while @@FETCH_STATUS=0
begin
select
name,salary
from
emp
where
salary >=20000
end
close mycur
deallocate mycur

---------------
Condition checks successfully and also run successfully.....
but infinite loop occurred..........
correct the error

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sksamantaray on: 12/20/2011 [Member] Silver | Points: 25

0	declare mycur cursor for select name,salary from emp open mycur declare @name varchar(50) declare @salary int fetch next from Mycur into @name,@salary while @@FETCH_STATUS=0 begin select name,salary from emp where salary >=20000 fetch next from Mycur into @name,@salary end close mycur deallocate mycur Try now Thanks, Sanjay mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

declare mycur
cursor
for
select
name,salary
from
emp
open mycur
declare @name varchar(50)
declare @salary int
fetch next from Mycur into @name,@salary
while @@FETCH_STATUS=0
begin
select
name,salary
from
emp
where
salary >=20000
fetch next from Mycur into @name,@salary
end
close mycur
deallocate mycur

Try now

Thanks,
Sanjay

mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: mareesnectar-12158 on: 12/20/2011 [Member] Starter | Points: 25

0	7 times duplication occured mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: mareesnectar-12158 on: 12/20/2011 [Member] Starter | Points: 25

0	Give me correct solution mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sksamantaray on: 12/20/2011 [Member] Silver | Points: 25

0	CREATE TABLE [dbo].[empl]( [sno] [int] NOT NULL, [Name] [varchar](50) NULL, [Company_name] [varchar](50) NULL, [salary] [int] NULL, CONSTRAINT [PK__emp__DDDF644603F0984C] PRIMARY KEY CLUSTERED ( [sno] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ----- Before sno Name Company_name salary 1 sanjay scalable-systems 40005 2 bijay scalable-systems 50005 3 ready scalable-systems 5005 ----------------------- 1 sanjay scalable-systems 40010 2 bijay scalable-systems 50010 3 ready scalable-systems 5010 Thanks, Sanjay mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

CREATE TABLE [dbo].[empl](
[sno] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Company_name] [varchar](50) NULL,
[salary] [int] NULL,
CONSTRAINT [PK__emp__DDDF644603F0984C] PRIMARY KEY CLUSTERED
(
[sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----
Before
sno Name Company_name salary
1 sanjay scalable-systems 40005
2 bijay scalable-systems 50005
3 ready scalable-systems 5005

-----------------------
1 sanjay scalable-systems 40010
2 bijay scalable-systems 50010
3 ready scalable-systems 5010

Thanks,
Sanjay

mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: mareesnectar-12158 on: 12/21/2011 [Member] Starter | Points: 25

0	correct the cursor program..... mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sksamantaray on: 12/21/2011 [Member] Silver | Points: 25

0	If the program is correct then click on login link to Mark As Answer in that section Thanks Thanks, Sanjay mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: mareesnectar-12158 on: 12/21/2011 [Member] Starter | Points: 25

0	correct the cursor program.. ... not table structure............... mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sksamantaray on: 12/21/2011 [Member] Silver | Points: 25

0	Its your table structure only, posted in the question. I have used it for your understanding. Thanks Thanks, Sanjay mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: mareesnectar-12158 on: 12/21/2011 [Member] Starter | Points: 25

0	My table structure CREATE TABLE emp( sno int primary key, Name varchar(50) , Company_name varchar(50) , salary int ) My cursor Program declare mycur cursor for select name,salary from emp open mycur declare @name varchar(50) declare @salary int fetch next from Mycur into @name,@salary while @@FETCH_STATUS=0 begin select name,salary from emp where salary >=20000 end close mycur deallocate mycur --------------- ERROR Condition checks successfully and also run successfully..... but infinite loop occurred.......... correct the error mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

My table structure
CREATE TABLE emp(
sno int primary key,
Name varchar(50) ,
Company_name varchar(50) ,
salary int )

My cursor Program

declare mycur
cursor
for
select
name,salary
from
emp
open mycur
declare @name varchar(50)
declare @salary int
fetch next from Mycur into @name,@salary
while @@FETCH_STATUS=0
begin
select
name,salary
from
emp
where
salary >=20000
end
close mycur
deallocate mycur

---------------
ERROR
Condition checks successfully and also run successfully.....
but infinite loop occurred..........
correct the error

mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksingh on: 12/21/2011 [Member] Starter | Points: 25

0	Hi, try it declare mycur cursor for select name,salary from emp open mycur declare @name varchar(50) declare @salary int fetch next from Mycur into @name,@salary while @@FETCH_STATUS=0 begin select name,salary from emp where salary >=20000 fetch next from Mycur into @name,@salary end close mycur deallocate mycur You have missed to fetch next data from cursor so it was checking your condition in infinite loop. Regards, Sunil mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
try it

declare mycur 

cursor 

for 

select 

name,salary 

from 

emp 

open mycur 

declare @name varchar(50) 

declare @salary int 

fetch next from Mycur into @name,@salary 

while @@FETCH_STATUS=0 

begin 

   select 

       name,salary 

       from 

        emp 

       where 

         salary >=20000 

fetch next from Mycur into @name,@salary  

end 

close mycur 

deallocate mycur

You have missed to fetch next data from cursor so it was checking your condition in infinite loop.

Regards,
Sunil

mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 12/21/2011 [Member] Silver | Points: 25

0	1.I am using same table only table name is different. 2.About your code: a) you haven't written Fetch.... statement before the while loop ends b)you have select query inside the body of the cursor, generally cursor is used for row by row data manipulation depending upon validation Thanks, Sanjay mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: mareesnectar-12158 on: 12/21/2011 [Member] Starter | Points: 25

0	i tried your code..... now 8 times loop ran...(print name salary 8 times) mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sksamantaray on: 12/21/2011 [Member] Silver | Points: 25

0	can you attach the output of select * from emp here. Thanks, Sanjay mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: mareesnectar-12158 on: 12/21/2011 [Member] Starter | Points: 25

0	1 marees TCS 500 2 gobi TCS 11000 3 pandi TCS 11000 7 kumar TCS 15000 8 karthick TCS 15000 11 mani TCS 11000 12 easwer TCS 11000 13 anju TCS 10000 15 malar TCS 11000 19 uma TCS 9000 mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: mareesnectar-12158 on: 12/21/2011 [Member] Starter | Points: 25

0	table output 1 marees TCS 500 2 gobi TCS 11000 3 pandi TCS 11000 7 kumar TCS 15000 8 karthick TCS 15000 11 mani TCS 11000 12 easwer TCS 11000 13 anju TCS 10000 15 malar TCS 11000 19 uma TCS 9000 Cursor OUTput 8 times loop ran...(print name salary 8 times) mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sksamantaray on: 12/21/2011 [Member] Silver | Points: 25

0	1 marees TCS 500 2 gobi TCS 11000 3 pandi TCS 11000 7 kumar TCS 15000 8 karthick TCS 15000 11 mani TCS 11000 12 easwer TCS 11000 13 anju TCS 10000 15 malar TCS 11000 19 uma TCS 9000 if it is your actual table then there will be no output only 10 blank tables will be displayed in sql because the main query of cursor has 10 row ,so loop will continue for 10 tmes. Then inside the loop you have written select name,salary where salary >20000 this will be executed 10 times so 10 blank tables will be output Thanks, Sanjay mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

1 marees TCS 500
2 gobi TCS 11000
3 pandi TCS 11000
7 kumar TCS 15000
8 karthick TCS 15000
11 mani TCS 11000
12 easwer TCS 11000
13 anju TCS 10000
15 malar TCS 11000
19 uma TCS 9000
if it is your actual table then there will be no output only 10 blank tables will be displayed in sql
because the main query of cursor has 10 row ,so loop will continue for 10 tmes.
Then inside the loop you have written select name,salary where salary >20000
this will be executed 10 times so 10 blank tables will be output

Thanks,
Sanjay

mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: mareesnectar-12158 on: 12/21/2011 [Member] Starter | Points: 25

0	yes...how to correct the query.... i want only one output reply me mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: mareesnectar-12158 on: 12/21/2011 [Member] Starter | Points: 25

0	yes...how to correct the query.... i want only output(salary>15000) using cursor without loop(only one time) reply me mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: mareesnectar-12158 on: 12/21/2011 [Member] Starter | Points: 25

0	Pls Give me solution mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sksamantaray on: 12/21/2011 [Member] Silver | Points: 25

0	In your situration since there are 2 employees getting salary of 15000 , so there will be atleast two records as output What is your exact requirement? Why are you user Cursor here? Thanks, Sanjay mareesnectar-12158, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts