sql - 删除 SQL Server 中的临时表？

Question

我在删除 SQL Server 中的临时表时遇到问题。

我有一个存储过程，但是当我通过我的应用程序运行时，它说:

"There is already an object named '#WeekList' in the database"

当我尝试删除表格时，我收到以下消息:

Cannot drop the table '#WeekList', because it does not exist or you do not have permission.

我的SP如下:

USE [test_staff]
GO
/****** Object:  StoredProcedure [dbo].[sp_create_week_list]    Script Date: 03/20/2012 09:35:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[sp_create_week_list]

AS

CREATE TABLE #WeekList
    (
    month_date date
    )


DECLARE @REPORT_DATE DATETIME, @WEEK_BEGINING VARCHAR(10)
SELECT @REPORT_DATE = '2011-01-19T00:00:00'
--SELECT @REPORT_DATE = GETDATE() -- should grab the date now.
SELECT @WEEK_BEGINING = 'MONDAY'
IF @WEEK_BEGINING = 'MONDAY'
SET DATEFIRST 1
ELSE IF @WEEK_BEGINING = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @WEEK_BEGINING = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @WEEK_BEGINING = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @WEEK_BEGINING = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @WEEK_BEGINING = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @WEEK_BEGINING = 'SUNDAY'
SET DATEFIRST 7
DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATE
SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1)
--GET THE WEEK END DATE
SELECT @WEEK_END_DATE = @REPORT_DATE + (7 - DATEPART(DW, @REPORT_DATE))
PRINT 'Week Start: ' + CONVERT(VARCHAR, @WEEK_START_DATE)
PRINT 'Week End: ' + CONVERT(VARCHAR, @WEEK_END_DATE)

DECLARE @Interval int = datediff(WEEK,getdate(),@WEEK_START_DATE)+1

SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
INTO #WeekList

WHILE @Interval <= 0
    BEGIN
    set @WEEK_START_DATE=DATEADD(WEEK,1,@WEEK_START_DATE)
    set @WEEK_END_DATE=DATEADD(WEEK,1,@WEEK_END_DATE)
    INSERT INTO #WeekList values (@WEEK_START_DATE,@WEEK_END_DATE)
    SET @Interval += 1;
    END

SELECT 
CONVERT(VARCHAR(11), Start_Week, 106) AS 'Start',
CONVERT(VARCHAR(11), End_Week, 106) AS 'End',
DATEDIFF(DAY, 0, Start_Week) / 7 AS week_ref   -- create the unique week reference number

FROM #WeekList
ORDER BY Start_Week DESC

DROP TABLE #WeekList

Answer 1

在 SP 中，以下语句存在问题。

SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
INTO #WeekList

与其尝试这个，不如试试这个，它可能会起作用。

INSERT INTO #WeekList
SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE