help with a cursor

[Crystalguru]

I am using MS SQL Server 2000

Can someone tell me why the following sql only returns the first cursor set?


ALTER PROC Top_3_Results_R_Pr
AS

-- variables for cursor-obtained values
DECLARE
@ChartGUID varchar(16)
,@ClientDisplayName varchar(50)
, @ClientGUID varchar(16)


-- make table for set of 3 Results
CREATE TABLE #TopResults (
ResultGUID numeric(16)
, ClientDisplayName varchar(50)
, ClientGUID varchar(16)
, ChartGUID varchar(16)
, ItemName varchar(40)
, ArrivalDTM datetime
, Value varchar(60)
)

-- get current client list into temp table - only patients that have active Diet Orders
SELECT DISTINCT
CV3ClientVisit.ChartGUID AS ChartGUID
, CV3ClientVisit.ClientDisplayName
, CV3ClientVisit.ClientGUID
INTO #client_info
FROM
CV3ClientVisit (nolock)
INNER JOIN CV3Order (nolock)
ON CV3ClientVisit.ClientGUID = CV3Order.ClientGUID
AND CV3ClientVisit.ChartGUID = CV3Order.ChartGUID
AND CV3Order.OrderStatusLevelNum = 50
INNER JOIN CV3OrderCatalogMasterItem OC (nolock)
ON CV3Order.OrdercatalogmasteritemGUID = OC.GUID
AND OC.OrgUnitGUID = 1006083001
OR OC.OrgUnitGUID = 1000001006083001

-- for each patient, get last 3 Results
DECLARE Cur_list CURSOR FOR
SELECT ChartGUID, ClientDisplayname,ClientGUID FROM #client_info

OPEN Cur_list


FETCH NEXT FROM Cur_list
INTO @ChartGUID
,@ClientDisplayName
, @ClientGUID

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TopResults
SELECT TOP 3
CV3BasicObservation.GUID
, CV3ClientVisit.ChartGUID
, CV3ClientVisit.ClientDisplayName
, CV3ClientVisit.ClientGUID
, CV3BasicObservation.ItemName
, CV3BasicObservation.ArrivalDTM
, CV3BasicObservation.Value
FROM CV3ClientVisit
INNER JOIN CV3BasicObservation -- not left outer
ON (CV3ClientVisit.ClientGUID = CV3BasicObservation.ClientGUID)
AND (CV3ClientVisit.ChartGUID = CV3BasicObservation.ChartGUID)
WHERE (
CV3ClientVisit.ChartGUID = @ChartGUID
AND CV3ClientVisit.ClientGUID = @ClientGUID
)
AND (
/* Result Name */
CV3BasicObservation.ItemName = 'Albumin'
)
ORDER BY CV3BasicObservation.ArrivalDTM DESC
FETCH NEXT FROM Cur_list
INTO @ChartGUID
,@ClientDisplayName
,@ClientGUID

END
CLOSE Cur_list
DEALLOCATE Cur_list
DECLARE Cur_list CURSOR FOR
SELECT ChartGUID, ClientDisplayName, ClientGUID FROM #client_info

OPEN Cur_list

FETCH NEXT FROM Cur_list
INTO @ChartGUID
,@ClientDisplayName
,@ClientGUID

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TopResults
SELECT TOP 3
CV3BasicObservation.GUID
, CV3ClientVisit.ChartGUID
, CV3ClientVisit.ClientDisplayName
, CV3ClientVisit.ClientGUID
, CV3BasicObservation.ItemName
, CV3BasicObservation.ArrivalDTM
, CV3BasicObservation.Value
FROM CV3ClientVisit
INNER JOIN CV3BasicObservation -- not left outer
ON (CV3ClientVisit.ClientGUID = CV3BasicObservation.ClientGUID)
AND (CV3ClientVisit.ChartGUID = CV3BasicObservation.ChartGUID)
WHERE (
CV3ClientVisit.GUID = @ChartGUID
AND CV3ClientVisit.ClientGUID = @ClientGUID
)
AND (
/* Result Name */
CV3BasicObservation.ItemName = 'Prealbumin'
)
ORDER BY CV3BasicObservation.ArrivalDTM DESC
FETCH NEXT FROM Cur_list
INTO @ChartGUID
,@ClientDisplayName
, @ClientGUID

END
CLOSE Cur_list
DEALLOCATE Cur_list

-- Get and return results
SELECT #TopResults.ResultGUID
, #TopResults.ChartGUID
, #TopResults.ClientDisplayName
, #TopResults.ClientGUID
, #TopResults.ResultGUID
, #TopResults.ItemName
, #TopResults.ArrivalDTM
, #TopResults.Value
FROM #TopResults
Order by 3, 6, 7 DESC