I have a query that I wrote in the Query Analyzer and it works great. So, I created a stored procedure and basicly cut and paste the query into the stored procedure to call from my application. When I run the query in the Query Analyzer, it returns 1 record in zero seconds. When I called the stored procedure from my application, it returned the same 1 record in 6 seconds. I tried calling the stored procedure from the QA and it took 6 seconds. So I decided to just use the:

TEXT lcCMD
ENDTEXT

and put my code it in, thinking, now it should be just like the QA, still 6 seconds. So, I'm missing something, I'm hoping someone can look at this a figure out what the difference is. I apologize for the length of this post, but I wanted to show all the code.

QA Code First (Zero seconds to run)

DECLARE @tiProcID int
DECLARE @iLength int
DECLARE @DateTimeBegin datetime
DECLARE @DateTimeEnd datetime
declare @tiPatID int
declare @tiDocID int
declare @ttEarlyStart datetime

SET @tiProcID = 38305
SET @DateTimeBegin = '09/09/2004'
SET @DateTimeEnd =   '09/09/2004'
set @ttEarlySTart='09/09/2004 09:30:00'
set @tiPatID=204532


set @iLength=(Select defaultTime from dbo.coProc where procID=@tiProcID)


SELECT top 1 RoomID,Roomname,DeptID,cast(StartTime as DateTime) as StartTime,
              cast(EndTime as DateTime) as EndTime,SlotOk
FROM
	(SELECT det.RoomID,det.RoomName,det.DeptID,det.StartTime,dateAdd(mi,det.DefaultTime,det.StartTime) as EndTime,0 as SlotOK,
	  (select count(meetingnumber) from dbo.meetings
		where (begintime between det.StartTime and dateadd(mi,@iLength,det.StartTime)
	        or     endtime   between det.StartTime and dateadd(mi,@iLength,det.StartTime)
	        	or det.StartTime between begintime and endtime
	        	or dateadd(mi,@ilength,det.StartTime) between begintime and endtime)
	        	and iscancelled=0 and roomid = det.RoomID ) as RoomConflict,
		(select count(meetingnumber) from dbo.meetings
			where (dateadd(mi,-1,begintime) between det.StartTime and dateadd(mi,@iLength,det.StartTime)
			or     endtime   between det.StartTime and dateadd(mi,@iLength,det.StartTime))
			and iscancelled=0 and patientID = @tiPatID) as PatientConflict
	FROM
		(SELECT distinct dbo.corooms.roomname, dbo.coproc.defaulttime, dbo.corooms.deptid, 
		                dbo.corooms.roomid, dbo.corooms.schedfrom, dbo.corooms.schedto,@DateTimeBegin+' '+StartTime as StartTime
		FROM   dbo.coproc INNER JOIN
		       	dbo.coProcRoomGrp ON dbo.coproc.procid = dbo.coProcRoomGrp.procID INNER JOIN
		 	dbo.coRoomToGroup ON dbo.coProcRoomGrp.coRoomGrpID = dbo.coRoomToGroup.coRoomGrpID INNER JOIN
		 	dbo.corooms ON dbo.coRoomToGroup.RoomID = dbo.corooms.roomid inner Join
			dbo.coProcStartTimes on coProc.ProcID=coProcStartTimes.ProcID
		WHERE  dbo.coproc.procid = @tiProcID and dbo.coRooms.Isactive=1 )det
	where det.StartTime > @ttEarlyStart)DET2
WHERE det2.RoomConflict=0 and PatientConflict=0
order by StartTime

Stored Procedure Code

CREATE PROCEDURE ctFTS5_B
@tiProcID int,
@DateTimeBegin varchar(15),
@DateTimeEnd varchar(15),
@ttEarlyStart datetime,
@tiPatID int,
@tiDocID int

as

DECLARE @iLength int

set @iLength=(Select defaultTime from dbo.coProc where procID=@tiProcID)

SELECT top 1 RoomID,Roomname,DeptID,cast(StartTime as DateTime) as StartTime,
              cast(EndTime as DateTime) as EndTime,SlotOk
FROM
	(SELECT det.RoomID,det.RoomName,det.DeptID,det.StartTime,dateAdd(mi,det.DefaultTime,det.StartTime) as EndTime,0 as SlotOK,
	  (select count(meetingnumber) from dbo.meetings
		where (begintime between det.StartTime and dateadd(mi,@iLength,det.StartTime)
	        or     endtime   between det.StartTime and dateadd(mi,@iLength,det.StartTime)
	        	or det.StartTime between begintime and endtime
	        	or dateadd(mi,@ilength,det.StartTime) between begintime and endtime)
	        	and iscancelled=0 and roomid = det.RoomID ) as RoomConflict,
		(select count(meetingnumber) from dbo.meetings
			where (dateadd(mi,-1,begintime) between det.StartTime and dateadd(mi,@iLength,det.StartTime)
			or     endtime   between det.StartTime and dateadd(mi,@iLength,det.StartTime))
			and iscancelled=0 and patientID = @tiPatID) as PatientConflict
	FROM
		(SELECT distinct dbo.corooms.roomname, dbo.coproc.defaulttime, dbo.corooms.deptid, 
		                dbo.corooms.roomid, dbo.corooms.schedfrom, dbo.corooms.schedto,@DateTimeBegin+' '+StartTime as StartTime
		FROM   dbo.coproc INNER JOIN
		       	dbo.coProcRoomGrp ON dbo.coproc.procid = dbo.coProcRoomGrp.procID INNER JOIN
		 	dbo.coRoomToGroup ON dbo.coProcRoomGrp.coRoomGrpID = dbo.coRoomToGroup.coRoomGrpID INNER JOIN
		 	dbo.corooms ON dbo.coRoomToGroup.RoomID = dbo.corooms.roomid inner Join
			dbo.coProcStartTimes on coProc.ProcID=coProcStartTimes.ProcID
		WHERE  dbo.coproc.procid = @tiProcID and dbo.coRooms.Isactive=1 )det
	where det.StartTime > @ttEarlyStart)DET2
WHERE det2.RoomConflict=0 and PatientConflict=0
order by StartTime
GO

Thanks for any help.

Kirk