Endless query after adding two variables

[PRMiller]

Ok, I'm back with my ugly stored procedure. The looping problem has been solved, but I've ran into another roadblock. As I loop incrementally through records in a temp table, I insert new records to fill in the gaps. Here's that snippet of code:

        INSERT INTO #TEMP1
          (intSCHED_ID, intAGENT_ID, datDATE, intStart_Minute,
          intStartSecond, sintEXCEPTION_CODE)
        SELECT  
          @iSchedID, @iNextAgentID, @datStartDate, (@iCurrentMinute + @iScheduleLength),
          ((@iCurrentMinute + @iScheduleLength) * 60), @sDefaultExcep

@iCurrentMinute and @iScheduleLength are both declared as integers, as are the two columns intStart_Minute and intStartSecond in the temporary table.

For some reason, I can perform the operation

((@iCurrentMinute + @iScheduleLength) * 60)

and insert it into a new record at the intStartSecond column, but I cannot simply insert

(@iCurrentMinute + @iScheduleLength)

into the intStart_Minute column. When I attempt to do this, the query just runs for ages, eventually timing out. I've tried inserting Print statements, but can't seem to figure out what's going wrong. I do not have permissions for the debugger.

I am baffled by this. I have double and triple-checked my temp table column data types and variables. Everything is set to integer. It seems odd to me that I can perform addition and multiplication, but not just addition.

I'm fairly convinced that I've missed something obvious, but I think I've just been staring at this for too long.

Any suggestions? Following is the code in its entirety:

--/*******************************************************************************
-- *  Created on:  11/11/2005
-- *  Last Updated:  11/11/2005
-- *  Created By:  xxxxxx
-- *
-- *  Purpose:  xxxxx Over/Under
-- *  Dependencies:  
-- *  History of Changes:
-- *                       
-- *
-- **/


--CREATE PROCEDURE [dbo].[usp_Over_Under_a] 

--  @datStartDate As datetime

--AS

Declare @datStartDate datetime
Set @datStartDate = '2005-11-18'

--Step 1 - Create temporary table populated with schedheader records
If Object_ID('tempdb..#TEMP1') Is Not Null
  Begin
    DROP TABLE #TEMP1
  End



CREATE TABLE #TEMP1
  (numKey_pk Numeric Identity (1,1) NOT NULL, intSched_ID Int, intAgent_ID Int, 
  datDate SmallDateTime, intStart_Minute Int, intStartSecond Int, 
  sintException_Code SmallInt, intSchedule_Length Int)

INSERT INTO #TEMP1
SELECT 
  intSCHED_ID,
  intAGENT_ID,
  datDATE, 
  sintSTART_MINUTE, 
  (sintSTART_MINUTE * 60),
  sintEXCEPTION_CODE, 
  sintSCHEDULE_LENGTH
FROM tbl_IEX_A_schedheader
WHERE 
  datDATE = @datStartDate
  AND intAgent_ID = 3



--Step 2 - Populate temp table with scheddetail records
SET NoCount On

Declare @iReturnCode Int
Declare @iNextAgentID Int
Declare @iCurrentAgentID Int
Declare @iLoopControl Int

Declare @iSchedID Int
Declare @sStartMinute SmallInt
Declare @sDefaultExcep SmallInt

--Initialize variables
SELECT @iLoopControl = 1
SELECT @iNextAgentID = Min(intAgent_ID)
FROM #TEMP1
SELECT @iCurrentAgentID = @iNextAgentID
FROM #TEMP1

SELECT 
  @iSchedID = intSched_ID, 
  @sStartMinute = intStart_Minute,
  @sDefaultExcep = sintException_Code
FROM #TEMP1
WHERE 
  intAGENT_ID = @iCurrentAgentID




--Start the main processing loop
While @iLoopControl = 1
  Begin
    INSERT INTO #TEMP1
      (intSched_ID, intAgent_ID, datDate, intStart_Minute, 
      intStartSecond, sintException_Code, intSchedule_Length)
    SELECT  
      intSCHED_ID, @iNextAgentID, @datStartDate, sintSTART_MINUTE,
      (sintSTART_MINUTE * 60), sintEXCEPTION_CODE, sintLENGTH
    FROM tbl_IEX_A_scheddetail
    WHERE
      intSCHED_ID = @iSchedID




    --Populate temp table with default exception code during gaps
    Declare @iStartSecond Int
    Declare @iExceptionCode Int
    Declare @iScheduleLength SmallInt

    Declare @iCurrentMinute Int
    Declare @iNextMinute Int
    Declare @iMinuteLoopControl Int
    Declare @iNewMinute Int

    SELECT @iMinuteLoopControl = 1
    SELECT @iNextMinute = Min(intSTART_MINUTE)
    FROM #TEMP1
    WHERE intSCHED_ID = @iSchedID

    SELECT @iCurrentMinute = @iNextMinute

    SELECT @iScheduleLength = intSchedule_Length
    FROM #TEMP1
    WHERE intSCHED_ID = @iSchedID
    AND intStart_Minute = @iCurrentMinute

    --Start the minute sub-processing loop
    While @iMinuteLoopControl = 1
      Begin

        INSERT INTO #TEMP1
          (intSCHED_ID, intAGENT_ID, datDATE, intStart_Minute,
          intStartSecond, sintEXCEPTION_CODE)
        SELECT  
          @iSchedID, @iNextAgentID, @datStartDate, (@iCurrentMinute + @iScheduleLength),
          ((@iCurrentMinute + @iScheduleLength) * 60), @sDefaultExcep


        --Reset minute looping variable
        SELECT @iNextMinute = 0

        SELECT @iNextMinute = Min(intSTART_MINUTE)
        FROM #TEMP1
        WHERE 
          intSCHED_ID = @iSchedID
          AND intSTART_MINUTE > @iCurrentMinute

        If IsNull(@iNextMinute,0) = 0
          Begin
	    Break
          End

        --Get the next minute
	SELECT @iCurrentMinute = sintSTART_MINUTE
	FROM tbl_IEX_A_scheddetail
	WHERE sintSTART_MINUTE = @iNextMinute

      End
select * from #TEMP1 order by intSTART_MINUTE
    Return

    --Reset looping variable
    SELECT @iNextAgentID = NULL
    
    SELECT @iNextAgentID = Min(intAGENT_ID)
    FROM #TEMP1
    WHERE 
      intAGENT_ID > @iCurrentAgentID

    If IsNull(@iNextAgentID,0) = 0
      Begin
        Break
      End


    --Get the next agent ID
    SELECT @iCurrentAgentID = intAGENT_ID
    FROM #TEMP1
    WHERE intAGENT_ID = @iNextAgentID

  End

Return

--GO

 

[BSman]

I don't know what the problem is, but have you tried this workaround solution:

((@iCurrentMinute + @iScheduleLength) * 1)

Bob S.