CODEWITHSUNDEEP
databaset-sqlsql-server-2005database-cursor
Dan Black
Dan Black

Reputation: 1217

Avoid SQL Cursor in this scenario

I have inherited a system which seemingly requires me to use a cursor or while loop. Given the below tables, I would like to get the names of the attendees e.g

Bill
Bob
Jane
Jill

Attendees
SourceTable|SourceTableId
Boys |1
Boys |2
Girls |2
Girls |1

Boys
Id|FirstName
1 |Bill
2 |Bob

Girls
Id|FirstName
1 |Jill
2 |Jane

Note, the system doesn't actually use Attendees,Boys & Girls but rather uses Contracts, Orders and other such entities etc but it was easier\simpler to represent in this form. There may be loads more lookup tables than just "boy" and "girl" so

Is there anyway I can achieve this by not using cursors or other row based operations.

Upvotes: 0

Views: 111

Answers (2)

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66702

A union is probably the only way you're going to do this, probably encapsulated in a view. If you can get a list of the tables then you could write a code generator that generates the view. If necessary put the view in a different database or schema on the same server if the vendor won't allow you to put it in the application DB.

Can you programatically identify the tables and columns you need or get a list from somewhere?

Upvotes: 0

DavidEG
DavidEG

Reputation: 5957

If I understand this query should work:

SELECT FirstName
  FROM Attendees
  join Boys on id = SourceTableId
 WHERE SourceTable = 'Boys'
union all
SELECT FirstName
  FROM Attendees
  join Girls on id = SourceTableId
 WHERE SourceTable = 'Girls'

Upvotes: 2

Related Questions