Correct the Cursor program

Posted by mareesnectar-12158 under Sql Server on 12/20/2011 | Points: 10 | Views : 1208 | Status : [Member] | Replies : 20
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response