Error regarding cursor in SQL

Question

Hi guys,

following is my stored procedure. It contains a subquery

 Select StartTime From DrTimings Where DrID = @DrID

If this subquery returns more than one value, I get an error. Subquery returns multiple rows. I want to get each @StartTime and @EndTime in cursor. Means I want to "fetch next from Doctor into @StTime and @EndTime"

Can I use two parameters in a cursor?

ALTER PROCEDURE [dbo].SP_AFK_GetSlotsByDate
    @DrID int,
    @AppointmentDate Datetime 
AS
BEGIN

Declare @StartTime Datetime
Declare @EndTime Datetime
BEGIN
    SET @StartTime = (Select StartTime From DrTimings Where DrID = @DrID)
    SET @EndTime =  (Select EndTime From DrTimings Where DrID = @DrID)
END

DECLARE Doctor CURSOR FOR

Select StartTime  from  TimeList1 where StartTime>=@StartTime and StartTime<@EndTime

Declare @StTime datetime
Declare @SlotID int
Declare @AppointmentTime datetime

Declare @TempSlots Table (SlotID int , AppointmentTime datetime null) 


Insert into
@TempSlots
(
SlotID ,
AppointmentTime
)
values(
0,
Getdate()
)

open Doctor
    fetch next from Doctor into @StTime
     while @@fetch_status = 0
      Begin

Select  @SlotID= T.SlotId from TimeList1 T
where T.StartTime>=@StartTime and T.StartTime<@EndTime and
T.SlotId not in 
     (Select A.SlotId from AppointmentSheet A where A.AppointmentDate=@AppointmentDate)

Select @AppointmentTime = Convert(varchar,right(T.StartTime,7),131)+' - '+ Convert(varchar,right(T.EndTime,7),131) 
from TimeList1 T
where T.StartTime>=@StartTime and T.StartTime<@EndTime and
T.SlotId not in 
     (Select A.SlotId from AppointmentSheet A where A.AppointmentDate=@AppointmentDate)

     Update @TempSlots
     Set SlotID  = @SlotID,
                AppointmentTime=@AppointmentTime

    fetch next from Doctor into @StTime
        end
    close Doctor
    deallocate Doctor

    Select * From @TempSlots
END

Answer 1

Just add the next variable this way:

fetch next from Doctor into @StTime, @EndTime

The select statement of your cursor should contain the column EndTime:

select StartTime, EndTime 
from   TimeList1 
where  StartTime>=@StartTime and StartTime<@EndTime

Answer 2

While cursors are an easy way to iterate through a result set, they are not recommended because of performance implications.

If I knew the structure of your TimeList1 table and it's relation to the DrTimings table, I would recommend a version that does not use cursors.

However, after reviewing your T-SQL, I decided to provide you with an updated version that reduces redundancies and uses JOINS instead of subqueries:

ALTER PROCEDURE [dbo].SP_AFK_GetSlotsByDate
(
    @DrID int,
    @AppointmentDate DateTime 
)
AS

DECLARE 
    @StartTime DateTime,
    @EndTime DateTime,
    @SlotID int,
    @AppointmentTime DateTime;

-- Retrieve the initial values for StartTime and EndTime
-- These values get overwritten by the cursor (?)
SELECT 
    @StartTime = StartTime,
    @EndTime = EndTime
FROM
    DrTimings
WHERE
    DrID = @DrID;


DECLARE @TempSlots TABLE
(
    SlotID int, 
    AppointmentTime datetime NULL
); 

-- Set default values
INSERT @TempSlots (SlotID,AppointmentTime)
VALUES (0, GETDATE());


DECLARE Doctor CURSOR FOR
SELECT 
    StartTime,
    EndTime  
FROM  
    TimeList1 
where 
    StartTime >= @StartTime AND 
    StartTime < @EndTime;

OPEN Doctor
FETCH NEXT FROM Doctor INTO @StartTime,@EndTime
WHILE @@FETCH_STATUS = 0
    BEGIN
     SELECT
      @SlotID = T.SlotId, 
      @AppointmentTime = CONVERT(varchar,RIGHT(T.StartTime,7),131)
             + ' - ' + CONVERT(varchar,RIGHT(T.EndTime,7),131) 
     FROM 
      TimeList1 T
      LEFT JOIN AppointmentSheet A ON T.SlotId = A.SlotId
     WHERE 
      T.StartTime >= @StartTime AND 
      T.StartTime < @EndTime AND
      A.AppointmentDate = @AppointmentDate AND
      A.SlotId IS NULL;

     -- This table will always be updated to contain the latest values
     -- it will contain only one row
     UPDATE 
      @TempSlots
     SET
      SlotID  = @SlotID,
      AppointmentTime = @AppointmentTime;

     FETCH NEXT FROM Doctor INTO @StartTime,@EndTime
    END

CLOSE Doctor
DEALLOCATE Doctor

-- Return results
SELECT 
    SlotId,
    AppointmentTime
FROM 
    @TempSlots;

Update: If the intent is to obtain the latest values for SlotId and AppointmentTime, then iteration is not even needed.