Help me say No to Cursors! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help me say No to Cursors!

Author

Topic

jm53671
Starting Member

8 Posts

Posted - 2002-01-10 : 11:40:13

I'm having a disagreement

with a fellow programmer over cursors. We have a specific dataset we would like to return, but can't figure out how to do it from a stored procedure w/o using cursors and while loops.

Here's the setup:
SQL Server 2000
Person_T(ID, name, etc)
Phone_T(ID, Person.ID, Active_bln, Primary_bln, PhoneNumber)
-only one record per person may have Primary_bln = T
-multiple records per person may have Active_bln = T

We want to return a dataset that contains only 1 record per person.
If the primary_bln field is true, then that's our phone record.
If the primary_bln field is false, then we want the first phone record for that person which is active. So the problem is that we are having trouble getting just the first Primary_bln = F and Active_bln = T record.

My fellow programmer can do it with a cursor.
The best that I can come up with in pure SQL is:
Select Info
From ~
where Primary_bln=F and Active_bln=T and Person.ID not in
(Select Person.ID from ~ where Primary_T = T and Active_bln = T)
union
Select info
from ~
where Primary_bln=T and Active_bln=T.
You can see this doesn't fit our requirement as it would return multiple Active_bln=T, Primary_bln=F records.

Can anyone help me prove that we can do this w/o a cursor?
jmaass@cfsi.net

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-10 : 12:04:01

This should do it, although it looks scary:

SELECT Pers.*, IsNull(Pri.PhoneNumber,Act.PhoneNumber) PhoneNumber
FROM Person_T Pers
LEFT JOIN (SELECT * FROM Phone_T WHERE Primary_bln=True) Pri ON (Pers.ID=Pri.PersonID)
LEFT JOIN (SELECT PersonID, PhoneNumber FROM Phone_T P INNER JOIN
(SELECT PersonID, Min(ID) MinID FROM Phone_T WHERE Primary_bln=False AND Active_bln=True GROUP BY PersonID) A ON (P.PersonID=A.PersonID AND P.ID=A.MinID) ) Act
ON (Pers.ID=Act.PersonID)

The 1st subquery pulls the primary rows. The 2nd subquery has a nested subquery in it. The nested subquery finds the minimum ID for each PersonID that has an active number, this would be the "first" active phone number. It then joins itself back to the Phone_T table in order to get the phone number for that ID.

These 2 subqueries are then LEFT JOINed to Person_T, and IsNull checks the Primary phone number; if there isn't one, it will substitute the phone number from the Active subquery.

This may not be syntactically correct (there is no boolean type in SQL Server, so the True and False values in the query will need to be modified), but the logic should work, maybe with some tweaking (I actually put in the GROUP BY this time Arnold, Todd!

I keep them busy by forgetting it most of the time)

Edited by - robvolk on 01/10/2002 12:48:15

jm53671
Starting Member

8 Posts

Posted - 2002-01-10 : 14:13:14

robvolk,
You are so the bomb! We owe you lunch if you're ever in Austin, TX.

I redid my test database to test your statement. The only problem I had was pulling persons who had no active numbers. In case you are interested:

Select PhoneList.Lname, PhoneList.Fname, PhoneList.PhoneReturn
from
(
SELECT Pers.Lname, Pers.FName, IsNull(Pri.Phone,Act.Phone) PhoneReturn
FROM Person_T Pers
LEFT JOIN
(SELECT * FROM Phone_T WHERE Primary_char='T') Pri
ON (Pers.Person_ID = Pri.Person_ID_FK)
LEFT JOIN
(
SELECT P.Phone_ID, P.Phone, P.Person_ID_FK
FROM Phone_T P
INNER JOIN
(SELECT Phone_T.Person_ID_FK, Min(Phone_T.Phone_ID) MinID
FROM Phone_T WHERE Primary_char='F' AND Active_char='T'
GROUP BY Person_ID_FK) A
ON (P.Person_ID_FK = A.Person_ID_FK AND P.Phone_ID = A.MinID)
)
Act
ON (Pers.Person_ID = Act.Person_ID_FK)
) PhoneList
where PhoneList.PhoneReturn is not null

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-10 : 16:23:22

I scared myself with this one:


SELECT Pers.*, SUBSTRING(MinPhone, 12, LEN(MinPhone)) PhoneNumber
FROM Person_T Pers
INNER JOIN (
  SELECT PersonID, MIN(CASE WHEN Primary_bln = 'T' THEN '0' ELSE '1' END
    + RIGHT('0000000000' + CAST(ID AS varchar),10) + PhoneNumber) MinPhone
  FROM Phone_T
  WHERE Active_bln = 'T'
  GROUP BY PersonID
) Ph ON Pers.ID = Ph.PersonID

The subquery squishes together something that (a) orders to give the required phone number as min (b) contains the phone number. Outer part pulls phone number back out. It assumes that ID is numeric and no longer than 10 digits (true for a non-negative integer(4)).

Edited by - Arnold Fribble on 01/10/2002 16:29:45

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-10 : 16:36:53

!

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-10 : 16:45:12

quote:

!

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-10 : 16:46:14

The signature was meant to say it all.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-10 : 18:42:13

Nigell - just between us commonwealth-ers...

I can understand you being down on cursors - and I love to see you show how you can do everything without them - I just don't get why it's soooooo important?

Have you had a bad experience or what?

mad dogs and englishmen...

spock
Starting Member

35 Posts

Posted - 2002-01-10 : 22:43:56

Hi,

I came up with this soln. and it seems to work. Am i correct in doing like this?

create table #Person_T(empID int, name_pers varchar(20))
create table #Phone_T(empID int, personID int, Active_bln varchar(1),
Primary_bln varchar(1), PhoneNumber int)

insert into #Person_T values (1,'a')
insert into #Person_T values (2,'b')
insert into #Person_T values (3,'c')
insert into #Person_T values (4,'d')

insert into #Phone_T values (1,1,'T','F',12345)
insert into #Phone_T values (2,1,'T','F',23451)
insert into #Phone_T values (3,2,'T','F',23454)
insert into #Phone_T values (4,2,'T','F',23455)
insert into #Phone_T values (4,3,'F','T',23456)
insert into #Phone_T values (4,4,'F','T',23457)

select distinct b.empId, case(Primary_bln)
when 'T' then phonenumber
else ( select top 1 phonenumber from #Phone_T where personId = b.empId and Active_bln='T')
end as 'PhonenNumber'
from #Phone_T a inner join #Person_T b on b.empId = a.personId

the output i got was

empId PhonenNumber
----------- ------------
1 12345
2 23454
3 23456
4 23457

Thanks
Kaushik

narendrareddy
Starting Member

6 Posts

Posted - 2002-01-11 : 01:58:04

rrb
i would suggest this link..
http://www.sqlteam.com/item.asp?ItemID=5761

spock
maybe a left outer join instead of inner join.
#Person_T b left outer join #Phone_T a on b.empId = a.personId.

I think your query would be a correlated query which could be very expensive. Arnolds' query looks cool to me.

spock
Starting Member

35 Posts

Posted - 2002-01-11 : 06:52:33

naren,

what difference does it make if i use a left outer join instead of an inner join ?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-11 : 11:07:57

rrb
>> Have you had a bad experience or what?

Just that if you find a system with a lot of unnecessary cursors it is usually an indication of lack of relational database experience. Use of cursors mean that the developers do not have to understand the structure of the database and usually end up with bad design, poor transaction control and extremely poor performance.
Once you define a cursor you are forcing the code to work on a single row - why bother with a relational database?
If you start with a condition that cursors are not allowed it forces people to think about the relationships involved and why the database is built in that way.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-13 : 17:43:14

nr -

OK I can see your point - in fact I'd add to it and say that existence of cursors in the code suggest the coder was probably not from a DB background at all...Consider me cursorless from here on in!!(actually I already am!)

Cheers

mad dogs and englishmen...

narendrareddy
Starting Member

6 Posts

Posted - 2002-01-15 : 02:43:53

spock:
So that you will get all person records even if they do not have any phone record.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources