SQL Server temp table cannot be created but doesn't exist

Question

Forgive me I rarely work with large SQL Server stored procedures but I am having quite the issue with this following stored procedure.

Basically I am trying to take a manually run query that includes unions and place it into a stored procedure with a cursor and return the results into a temp table. However, I am receiving an error that the temp table cannot be created because it "already exists" (which I have run a check to see if it does exist, which It does not) or I do not have permission (which I do because I am logged in as the SQL admin). What on earth am I doing wrong?

I ran the follow query to see if it does exist within both tempdb and where the stored procedure is being create and it returns the else statement:

IF OBJECT_ID('IPAM..#tempschedule') IS NOT NULL
BEGIN
    PRINT '#temp exists!'
END
ELSE
BEGIN
    PRINT '#temp does not exist!'
END

Here is the stored procedure that I am trying to ALTER:

ALTER PROCEDURE lakearrowhead_schedule_python
AS
    IF OBJECT_ID('tempdb..#tempschedule') IS NOT NULL
    BEGIN
        DROP TABLE #tempschedule;
    END;

    CREATE TABLE #tempschedule
    (
        ProgramID INT,
        ItemID INT,
        Day SMALLINT,
        [Date] DATE,
        [Begin TIME] VARCHAR(15),
        [End TIME] VARCHAR(15),
        TIMESLOT NVARCHAR(11),
        SLOTTYPE INT,
        SlotTypeDescription NVARCHAR(64),
        SLOTINFO INT,
        SlotInfoDescription NVARCHAR(500),
        TalkID INT,
        Talk NVARCHAR(50),
        FIRSTNAME NVARCHAR(20),
        LASTNAME NVARCHAR(50),
        INSTITUTION NVARCHAR(150),
        AUTHORLINE NVARCHAR(384),
        TITLE NVARCHAR(256),
        Abstract VARCHAR(1),
        [LOCATION] INT,
        ADDENDUM TEXT,
        EventName NVARCHAR(256)
    );

    DECLARE @pid INT;

    DECLARE PROGRAM_cursor CURSOR FOR
        SELECT TOP 3 WebEvents.EventID
        FROM WebEvents
        INNER JOIN dbo.WebPrograms ON dbo.WebEvents.EventID = dbo.WebPrograms.ProgramID
        WHERE EventLocation = 1004
          AND (eventstartdate >= GETDATE())
        ORDER BY 
            eventstartdate, ProgramCode;

    OPEN PROGRAM_cursor;

    FETCH NEXT FROM PROGRAM_cursor INTO @pid;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT *
        INTO #tempschedule
        FROM 
            (SELECT 
                 WS.ProgramID ,
                 WS.ItemID ,
                 WS.Day ,
                 CASE DATEPART(WEEKDAY, DATEADD(DAY, WS.Day - 1, WE.eventstartdate))
                     WHEN 1 THEN 'Sun'
                     WHEN 2 THEN 'Mon'
                     WHEN 3 THEN 'Tue'
                     WHEN 4 THEN 'Wed'
                     WHEN 5 THEN 'Thu'
                     WHEN 6 THEN 'Fri'
                     WHEN 7 THEN 'Sat'
                     ELSE 'N/A'
                 END + ' ' + CONVERT(CHAR(12), DATEADD(DAY, WS.Day - 1, WE.eventstartdate), 101) AS Date,
                 CONVERT(VARCHAR(15), CONVERT(TIME, SUBSTRING(WS.TimeSlot, 0, 6)), 100) AS 'Begin TIME',
                 CONVERT(VARCHAR(15), CONVERT(TIME, SUBSTRING(WS.TimeSlot, 7, 6)), 100) AS 'End TIME',
                 WS.TimeSlot,
                 WS.SlotType,
                 WSSTC.Description AS SlotTypeDescription,
                 WS.SlotInfo,
                 WSSIC.Description AS SlotInfoDescription,
                 ISNULL(WSI.TalkID, 0) AS TalkID,
                 LEFT(WPI.FirstName, 10) + ' '
                     + LEFT(WPI.LastName, 10) + ' ('
                     + LEFT(WEA.institution, 10) + ')' + ' - '
                     + LEFT(WSI.Title, 10) + '...'
                     + RIGHT(WSI.Title, 10) AS Talk,
                 WPI.FirstName,
                 WPI.LastName,
                 WEA.institution,
                 WSI.AuthorLine,
                 WSI.Title,
                 LEFT(WSI.Abstract, 1) AS Abstract,
                 WS.Location,
                 WS.Addendum,
                 WE.EventName
             FROM   
                 dbo.WebSpeakerInfo WSI
             INNER JOIN 
                 dbo.WebPersonalInfo WPI ON WSI.UserID = WPI.UserID
             INNER JOIN 
                 dbo.webeventaffiliation WEA ON WPI.UserID = WEA.userid
                                             AND WEA.eventid = @pid
             RIGHT OUTER JOIN 
                 dbo.WebScheduleSlotTypeCodes WSSTC 
             RIGHT OUTER JOIN 
                 dbo.WebSchedules WS ON WSSTC.SlotType = WS.SlotType 
                 ON WSI.TalkID = WS.TalkID
             LEFT OUTER JOIN 
                 dbo.WebScheduleSlotInfoCodes WSSIC ON WS.SlotInfo = WSSIC.SlotInfo
             INNER JOIN 
                 dbo.WebEvents WE ON WE.EventID = @pid
             WHERE  
                 WS.ProgramID = @pid
                 AND WS.SlotType <> 6 ) AS x
            ORDER BY 
                 Day, TimeSlot;

        FETCH NEXT FROM PROGRAM_cursor INTO @pid;
    END;

    CLOSE PROGRAM_cursor;
    DEALLOCATE PROGRAM_cursor;

    SELECT *
    FROM #tempschedule;

    DROP TABLE #tempschedule;

Answer 1

When you run the ALTER statement, you are turning the stored procedure into this statement:

ALTER PROCEDURE lakearrowhead_schedule_python
AS
    IF OBJECT_ID('tempdb..#tempschedule') IS NOT NULL
        BEGIN
            DROP TABLE #tempschedule;
        END;

The rest of the statements are just executed after the ALTER.

Moral of the story? Always use BEGIN/END blocks for stored procedures, functions, and trigger.

I should note that temporary tables are deleted automatically when the stored procedure ends. You can be even more certain by using a table variable. These are both deleted and out-of-scope. So, explicitly deleting temporary tables is not necessary within the body of a stored procedure.