无法创建 SQL Server 临时表，但该表不存在

Question

请原谅我很少使用大型SQL Server存储过程，但是我在以下存储过程中遇到了很大的问题。

基本上，我正在尝试采用包含联合的手动运行的查询，并将其放入带有游标的存储过程中，并将结果返回到临时表中。但是，我收到一个错误，指出无法创建临时表，因为它"已经存在"(我已经运行了检查以查看它是否存在，它不存在(或我没有权限(我这样做是因为我以 SQL 管理员身份登录(。我到底做错了什么？

我运行了以下查询以查看它是否确实存在于 tempdb 中以及创建存储过程的位置，它返回 else 语句：

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

这是我尝试更改的存储过程：

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

运行 ALTER 语句时，会将存储过程转换为以下语句：

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

其余语句只是在ALTER之后执行。

故事的寓意？ 始终对存储过程、函数和触发器使用 BEGIN/END 块。

我应该注意，当存储过程结束时，临时表会自动删除。 通过使用表变量，您可以更加确定。 这些都已删除且超出范围。 因此，在存储过程的主体中显式删除临时表不是必需的。