Version SQL Server 2000

I apogize for the long post here, but this is a stored procedure that we call from our VFP application. Yesterday, a client said this report was returning the right number of records, but the wrong data in two of the fields, the Surgeon Name and Primary Procedure field. I logged into their system using Remote Desktop and ran the application, and sure enough, it was wrong, not only was it wrong, but to return 20 records it took over 2 minutes. Everything else is running correctly. So the first thing I did was back up their database and pulled it back to our shop. I restored it here expecting to see the same thing. But it ran perfect here and returned in 1 second. So I thought, well, when I backed it up and restored it, it must have cured any index problems. So last night I backed up their system again, dropped the database and restored it on their system, but still had the same result. I then copied the code (while logged into their system) and ran stored procedure code in the QA, it produced the same wrong results. So I ran the first part of the stored procedure seperate (the query that loads the cursor) and it returned the correct results. So somehow on their system, it is returning the wrong data. The even stranger part is the two fields that are incorrect I do not modify while manuiplating the cursor. I ran DBCC dbCheck and it didn't show anything being wrong.

I'm hoping that someone might have some clues where I should start looking.

Thanks for any help

Kirk Kelly

CREATE PROCEDURE ct_Get_CaseAnalysis3

@tiDeptID int, @ltQueryFrom datetime, @ltQueryTo DateTime

as

declare @include bit
set @include=1

declare @Results TABLE (meetingnumber int,casedate varchar(12),mpinumber varchar(25),
        PatientName varchar(50), PrimarySurgeon varchar(50), PrimaryProcedure varChar(100),
        RoomName varchar(50), ScheduledStart DateTime, EnterOR dateTime,ExitOR datetime,
	PRocStart DateTime, ProcEnd DateTime, RoomOpens int, RoomClosed int, WheelsTimeUsedMins int,
	caseDelayMins int, TurnMinutes int, RoomOpensChar varchar(20), RoomCloseChar varChar(20),firstCase bit,
	IncludeInRpt bit, SurgeonInRoom DateTime)
           

declare xCases cursor LOCAL READ_ONLY for           
SELECT top 100 percent dbo.meetings.meetingnumber,
	convert(varchar(12),dbo.meetings.Begintime,101) as CaseDate,
	dbo.patients.mpinumber,
	RTRIM(dbo.patients.lastname) + ', ' + RTRIM(dbo.patients.firstname) AS PatientName, 
	(SELECT  top 1 RTRIM(dbo.coper.lastname) + ', ' + RTRIM(dbo.coper.firstname) AS surgeonName
	   FROM dbo.meetings m INNER JOIN
                dbo.meetproc ON m.meetingnumber = dbo.meetproc.meetingnumber INNER JOIN
                dbo.coper ON dbo.meetproc.coperid = dbo.coper.coperid
                where m.meetingnumber=meetings.meetingnumber
                order by meetproc.sysorder) as PrimarySurgeon,
	(SELECT  top 1 Description 
	   FROM dbo.meetings m INNER JOIN
                dbo.meetproc ON m.meetingnumber = dbo.meetproc.meetingnumber
                where m.meetingnumber=meetings.meetingnumber
                order by meetproc.sysorder) as PrimaryProcedure,
	dbo.corooms.roomname, 
	dbo.meetings.begintime as ScheduledStart, 
        dbo.meetings.enteror, 
	dbo.meetings.exitor, 
	dbo.meetings.procstart, 
        dbo.meetings.procend,
	dbo.coRooms.DeptDisplayOrder,
	dbo.coRooms.SchedFrom as RoomOpens,
	dbo.coRooms.SchedTo   as RoomClosed,
	datediff(mi,enteror,exitor) as WheelsTimeUsedMins,
        datediff(mi,BeginTime,SurgeonInRoom) as CaseDelayMins,
	0 as turnMinutes,
	' ' as RoomOpensChar,
	' ' as RoomCloseChar,
	dbo.meetings.SurgeonInRoom
FROM    dbo.meetings INNER JOIN
        dbo.patients ON dbo.meetings.patientid = dbo.patients.patientid INNER JOIN
        dbo.corooms ON dbo.meetings.ActualRoomid = dbo.corooms.roomid
where iscompleted=1
and meetings.DeptID=@tiDeptID
and meetings.iscancelled=0
and meetings.isblock=0
and meetings.begintime between @ltQueryFrom and @ltQueryTo
and meetings.RoomClosed=0
and meetings.ERCase=0
AND datepart(dw,Meetings.BeginTime) between 2 and 6
order by convert(varchar(12),EnterOR,101),DeptDisplayOrder,EnterOR


declare @meetingnumber int,
@casedate varchar(12),@mpinumber varchar(25),
@PatientName varchar(50), @PrimarySurgeon varchar(50), @PrimaryProcedure varChar(100),
@RoomName varchar(50), @ScheduledStart DateTime, @EnterOR dateTime,@ExitOR datetime,
@PRocStart DateTime, @ProcEnd DateTime, @DeptDisplayOrder int,@RoomOpens int, @RoomClosed int, @WheelsTimeUsedMins int,
@caseDelayMins int, @TurnMinutes int, @RoomOpensChar varchar(20), @RoomCloseChar varChar(20),@SurgeonInRoom DateTime

declare @tiCases Int

declare @tcLastRoom varchar(50)
declare @tcLastDate varchar(10)
declare @tcLastExit datetime
declare @tiTurnMin int
set @tiTurnMin=0
set @tcLastDate='XXXXXXXXXX'
set @tcLastRoom='XXXXXX'



declare @tiCtr int
declare @FirstRecord bit
declare @RoomRow int
set @RoomRow=1
set @FirstRecord=1
set @tiCtr=1
set nocount on
Open xCases 
set @ticases=@@cursor_rows
--print 'tiCounter= '+cast(@tiCtr as varchar(10))
--print 'tiCases ='+cast(@tiCases as varchar(10))
while (@tiCtr <= @tiCases)
Begin
   fetch xCases into @meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
	@RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@DeptDisplayOrder,@RoomOpens,@RoomClosed, 
	@WheelsTimeUsedMins,@caseDelayMins,@TurnMinutes,@RoomOpensChar,@RoomCloseChar,@SurgeonInRoom

   -- Increment the counter
   set @tiCtr = @tiCtr+1
   if (@firstRecord=1)
   begin
	set @FirstRecord = 0
	insert into @Results 
	(meetingnumber,casedate,mpinumber,PatientName, PrimarySurgeon, PrimaryProcedure,
        RoomName, ScheduledStart,EnterOR,ExitOR,ProcStart,ProcEnd,RoomOpens, RoomClosed,WheelsTimeUsedMins,
	caseDelayMins, TurnMinutes, RoomOpensChar, RoomCloseChar,firstCase,IncludeInRpt,SurgeonInRoom) values
	(@meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
	@RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@RoomOpens,@RoomClosed, 
	@WheelsTimeUsedMins,@caseDelayMins,@TurnMinutes,@RoomOpensChar,@RoomCloseChar,1,1,@SurgeonInRoom)
	set @tcLastDate=@CaseDate
	set @tcLastRoom=@RoomName
	set @tcLastExit=@ExitOR
   end
   else
     if (@CaseDate<>@tcLastDate)
     begin
	insert into @Results 
	(meetingnumber,casedate,mpinumber,PatientName, PrimarySurgeon, PrimaryProcedure,
        RoomName, ScheduledStart,EnterOR,ExitOR,ProcStart,ProcEnd,RoomOpens, RoomClosed,WheelsTimeUsedMins,
	caseDelayMins, TurnMinutes, RoomOpensChar, RoomCloseChar,firstCase,IncludeInRpt,SurgeonInRoom) values
	(@meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
	@RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@RoomOpens,@RoomClosed, 
	@WheelsTimeUsedMins,@caseDelayMins,@TurnMinutes,@RoomOpensChar,@RoomCloseChar,1,1,@SurgeonInRoom)
	set @tcLastDate=@CaseDate
	set @tcLastRoom=@RoomName
	set @tcLastExit=@ExitOR
   end
   else
	if (@RoomName<>@tcLastRoom)
	begin
		insert into @Results 
		(meetingnumber,casedate,mpinumber,PatientName, PrimarySurgeon, PrimaryProcedure,
	      	RoomName, ScheduledStart,EnterOR,ExitOR,ProcStart,ProcEnd,RoomOpens, RoomClosed,WheelsTimeUsedMins,
		caseDelayMins, TurnMinutes, RoomOpensChar, RoomCloseChar,firstCase,IncludeInRpt,SurgeonInRoom) values
		(@meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
		@RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@RoomOpens,@RoomClosed, 
		@WheelsTimeUsedMins,@caseDelayMins,@TurnMinutes,@RoomOpensChar,@RoomCloseChar,1,1,@SurgeonInRoom)
		set @tcLastDate=@CaseDate
		set @tcLastRoom=@RoomName
		set @tcLastExit=@ExitOR
	end
	else
	begin
		set @tiTurnMin=DateDiff(mi,@tcLastExit,@EnterOR)
		insert into @Results 
		(meetingnumber,casedate,mpinumber,PatientName, PrimarySurgeon, PrimaryProcedure,
	      	RoomName, ScheduledStart,EnterOR,ExitOR,ProcStart,ProcEnd,RoomOpens, RoomClosed,WheelsTimeUsedMins,
		caseDelayMins, TurnMinutes, RoomOpensChar, RoomCloseChar,firstCase,IncludeInRpt,SurgeonInRoom) values
		(@meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
		@RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@RoomOpens,@RoomClosed, 
		@WheelsTimeUsedMins,@caseDelayMins,@tiTurnMin,@RoomOpensChar,@RoomCloseChar,0,1,@SurgeonInRoom)
		set @tcLastDate=@CaseDate
		set @tcLastRoom=@RoomName
		set @tcLastExit=@ExitOR
	end 
end

select * from @Results

-- Close the Cursor
Close xcases
DeAllocate xCases
GO