Cursor Issue

Question

Cursor Issue

pavithrareddyc

SSC Enthusiast

Points: 109
More actions
October 14, 2013 at 2:21 am

#299183

Hello,
I am using cursor for the first time. I have to print employee first and last name whose salary is less than the average salary of the corresponding department and its start day before 1/1/1990 Using Cursor in sql server 2008.
I could able to display the result using below query,but problem is output is displaying 6 times (from this query result is 6 rows : select id, Fname, Lname from employees where startdate <='1990-01-01') same result.
I want to display it only once.
Please help me.
Thanks in advance.
Logic:
declare @id int
declare @Fname varchar (50)
declare @Lname varchar (50)
declare C_employees cursor for
select id, Fname, Lname from employees
where startdate <='1990-01-01'
--and id is not null and fname is not null and lname is not null
open C_employees
fetch next from c_employees into @id,@Fname,@Lname
WHILE (@@FETCH_STATUS = 0)
BEGIN
select distinct A.id,A.FName,A.Lname from Employees A
inner join
(
select department,AVG(Salary) as ASalary from employees
where startdate <='1990-01-01'
group by department
)B
on A.department=B.department
where A.salary<B.ASalary
and A.startdate <='1990-01-01'
order by A.id desc
FETCH NEXT from C_employees into @id,@Fname,@Lname
END
CLOSE C_employees
DEALLOCATE C_employees

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Any reason why you need to use a cursor?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pavithrareddyc SSC Enthusiast Points: 109 More actions · Answer 2

I wanted to try using cursor. I know that performance is poor but still i want to implement the logic using cursor.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

pavithrareddyc (10/14/2013)
I wanted to try using cursor. I know that performance is poor but still i want to implement the logic using cursor.

You can start by providing table DDL, sample data in the form of INSERT statements and the desired output. 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pavithrareddyc SSC Enthusiast Points: 109 More actions · Answer 4

table :

id int Checked

FName varchar(50)Checked

LName varchar(50)Checked

Salary money Checked

department varchar(50)Checked

startdate date Checked

Sample data :select * from Employees

idFNameLNameSalarydepartment startdate

1pavi Gur 100.00software developer1991-01-01

2pra cha 200.00sr software developer1989-01-01

3tom cruise 300.00pharmacist 1980-01-01

4sandeepGur 200.00java developer 1994-01-01

5pavi cha 300.00sr software developer1987-01-01

6sandy maram400.00sr software developer1984-01-01

7indu maram400.00java developer 1984-01-01

8henry chan 500.00java developer 1982-01-01

output from Cursor:

repeating same output 6 times

idFNameLname

7indu maram

2pra cha

idFNameLname

7indu maram

2pra cha

idFNameLname

7indu maram

2pra cha

idFNameLname

7indu maram

2pra cha

idFNameLname

7indu maram

2pra cha

idFNameLname

7indu maram

2pra cha

Thanks.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

Please read the first article in my signature on how to post questions. 😉

It will give you more info on how to post table definitions, sample data etc.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

You are executing exactly the same query 6 times because of the cursor. There is absolutely no reason to use a cursor for this. Why would you want to use a cursor when you have acknowledged that you are aware of the poor performance of using one.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]