Combine SELECT UNION with Cursor 1

[Saners]

I am developing SQL reports in Visual Studio 2005 and have written a report that processes the following records.

1. A case has 1 case record.
2. A case record has 1 or more related service records.
3. A case record has 1 or more related activity records.
4. A case record has 1 or more related meeting records.
5. A service record has 1 or more related activity records.
6. A service record has 1 or more related meeting records.

I have written code that works to return all activities and meetings for a Case. This is the code:

DECLARE @strCaseid AS varchar(20)


-- 1. Get activities and meetings by Case - a case has 1 Case ID

SELECT Subject, DocType, CreationDate, CreationBy

FROM
(

---get all activities for the case

SELECT subject AS Subject, activitytypecodename AS DocType, createdon AS CreationDate,
createdbyname AS CreationBy

FROM FilteredActivityPointer
WHERE (regardingobjectid = @strCaseid)
UNION

---get all meetings for the case

SELECT '' AS Subject, 'Meeting' AS Expr1, createdon, createdbyname
FROM FilteredNew_meeting
WHERE (new_casenoid = @strCaseid)

) AS A

ORDER BY CreationDate DESC

I have written code to return all activities and meetings for a Service. This is the code:


DECLARE @ServiceName AS varchar(20)

-- 2. Get activities and meetings by Service - a case has 1 or more services so loop through a cursor

BEGIN
-- Set up cursor to loop through all service records

DECLARE curService CURSOR FOR
SELECT new_case
FROM filterednew_service
WHERE (new_caseid = @strCaseid)

OPEN curService
FETCH NEXT FROM curService into @ServiceName

-- Loop through all the activities for all the services in the cursor
WHILE @@FETCH_STATUS = 0
BEGIN

---get all activities for all services

SELECT subject AS Subject, activitytypecodename AS DocType, createdon AS CreationDate, createdbyname AS CreationBy
FROM FilteredActivityPointer
WHERE (regardingobjectidname = @ServiceName)

---get all meetings for all services

SELECT '' AS Subject, 'Meeting' AS Expr1, createdon, createdbyname
FROM FilteredNew_meeting
WHERE (new_regardingobjectidname = @ServiceName)


-- Get next service record
FETCH NEXT FROM curService into @ServiceName
END -- End of cursor loop
CLOSE curService
DEALLOCATE curService

END

When using an example of 1 Case record with 2 Service records the cursor only processes 1 Service record. It does not loop through all the service records. So the cursor processing isn't working properly.

I need to combine both parts of this code into one so that I return a single redordset. Any help or suggestions on a better way to achieve this will be much appreciated.


Thank you.

Saners.

 

[SQLSister]

STOP! Go read about how to properly access data using joins. NEVER use a cursor that runs row by row to do such a thing! You are so poorly educated, that I would not permit you to write any queries against my databases. Go back to school and relearn basic database queries.

Honestly, if you are in a professional position where you write SQL you should be fired, that is how bad this is.

"NOTHING is more important in a database than integrity." ESquared

 

[Saners]

Thanks for the reply. Message received loud and clear and advice followed. It has been a long while but I understand the strong response. I actually have resolved this with a simple join.

 

[Emtucifor]

Saners, I applaud you for your good attitude in your response. I would probably not have handled it as well.

SQLSister's answer, though harsh, wasn't really misplaced...