sql-server - 存储过程中的删除表无法正常工作？

Question

我有一个存储过程，如果它存在，它会删除一个表，然后重新创建该表并用相关数据填充它，我的一个 friend 有大致相同的代码，唯一真正的区别在于列标题 table 。

作为示例，这是我的外观(不是真的，只是一个表示)。

+----+-----+-----+--------+
| ID | Foo | Bar | Number |
+----+-----+-----+--------+
|  1 | x   | x   |      0 |
|  2 | x   | x   |      1 |
+----+-----+-----+--------+

这是他的样子

+----+--------+--------+-----+--------+
| ID | BarFoo | FooBar | Num | Suffix |
+----+--------+--------+-----+--------+
|  1 | x      | x      |   0 | a      |
|  2 | x      | x      |   1 | b      |
+----+--------+--------+-----+--------+

同样，这些只是对情况的陈述。

因为这是一项学校作业，老师将创建和执行两个 SP，但是在使用另一个 SP 后创建 SP 时，我收到此错误:

Msg 207, Level 16, State 1, Procedure XYZ, Line 59
Invalid column name 'Foo'.

Msg 213, Level 16, State 1, Procedure XYZ, Line 61
Column name or number of supplied values does not match table definition.

但是，在两个存储过程的开始处，我们都有:

CREATE PROCEDURE XYZ
AS
BEGIN
    IF EXISTS (SELECT name
               FROM   sysobjects
               WHERE  name = 'TABLENAME'
                      AND xtype = 'u')
        DROP TABLE TABLENAME;

据我了解，这应该删除整个表？包括表/列定义和数据？

到目前为止我发现的唯一修复方法是在创建存储过程之前单独执行 DROP TABLE，这对我们不起作用，因为它确实必须在存储过程中程序。

帮助将不胜感激:)

编辑:这是我的实际代码，除了注释，这正是它在我的脚本中的样子(不包括它背后的其他代码)。

IF EXISTS (SELECT name
           FROM   sysobjects
           WHERE  name = 'BerekenStatistiek'
                  AND xtype = 'p')
    DROP PROCEDURE BerekenStatistiek;


GO
CREATE PROCEDURE BerekenStatistiek
@jaar INT=0
AS
BEGIN
    IF EXISTS (SELECT name
               FROM   sysobjects
               WHERE  name = 'Statistiek'
                      AND xtype = 'u')
        DROP TABLE Statistiek;
    DECLARE @year AS NVARCHAR (4);
    SET @year = CONVERT (NVARCHAR (4), @jaar);
    SELECT *,
           CAST (Kost - Korting + Freight AS MONEY) AS Netto,
           '' AS Richting
    INTO   Statistiek
    FROM   (SELECT   O.Kwartaal,
                     CAST (SUM(O.Kost) AS MONEY) AS Kost,
                     CAST (SUM(O.Korting) AS MONEY) AS Korting,
                     CAST (SUM(O.Freight) AS MONEY) AS Freight
            FROM     (SELECT CASE 
WHEN CONVERT (NVARCHAR (8), OrderDate, 112) BETWEEN @year + '0101' AND @year + '0331' THEN 1 
WHEN CONVERT (NVARCHAR (8), OrderDate, 112) BETWEEN @year + '0401' AND @year + '0630' THEN 2 
WHEN CONVERT (NVARCHAR (8), OrderDate, 112) BETWEEN @year + '0701' AND @year + '0930' THEN 3 
WHEN CONVERT (NVARCHAR (8), OrderDate, 112) BETWEEN @year + '1001' AND @year + '1231' THEN 4 
END AS 'Kwartaal',
                             ROUND(UnitPrice * Quantity, 2) AS Kost,
                             Round((UnitPrice * Quantity) * Discount, 2) AS Korting,
                             Freight
                      FROM   Orders AS O
                             INNER JOIN
                             OrderDetails AS Od
                             ON O.OrderID = Od.OrderID
                      WHERE  CONVERT (NVARCHAR (4), OrderDate, 112) = @year) AS O
            GROUP BY O.Kwartaal) AS O1;
    ALTER TABLE Statistiek ALTER COLUMN Kwartaal INT NOT NULL;
    ALTER TABLE Statistiek ALTER COLUMN Richting NVARCHAR (8);
    ALTER TABLE Statistiek
        ADD PRIMARY KEY (Kwartaal);
...

这是他的代码(为了可读性，在变量中插入值被排除在外(他的代码有点笨重):

IF EXISTS (SELECT name
           FROM   sysobjects
           WHERE  name = 'BerekenStatistiek'
                  AND xtype = 'p')
    BEGIN
        DROP PROCEDURE BerekenStatistiek;
    END


GO
CREATE PROCEDURE BerekenStatistiek
@jaartal INT
AS
BEGIN
    DECLARE @huidigkwartaal AS INT = 1;
    DECLARE @beginmaand AS INT;
    DECLARE @eindmaand AS INT;
    DECLARE @vorige_netto_ontvangsten AS MONEY;
    IF EXISTS (SELECT *
               FROM   sysobjects
               WHERE  name = 'Statistiek'
                      AND xtype = 'U')
        BEGIN
            DROP TABLE Statistiek;
        END
    CREATE TABLE Statistiek
    (
        kwartaalnummer         INT          ,
        beginmaand             INT          ,
        eindmaand              INT          ,
        orderbedrag            MONEY        ,
        korting                MONEY        ,
        vervoerskost           MONEY        ,
        netto_ontvangsten      MONEY        ,
        stijgend_dalend_gelijk NVARCHAR (10)
    );

    --Variables get their data here.

    INSERT  INTO Statistiek (kwartaalnummer, beginmaand, eindmaand, orderbedrag, korting, vervoerskost, netto_ontvangsten, stijgend_dalend_gelijk)
    VALUES                 (@huidigkwartaal, @beginmaand, @eindmaand, @orderbedrag, @korting, @vervoerskost, @netto_ontvangsten, @stijgend_dalend_gelijk);

Answer 1

“但是，在使用另一个 SP 后创建时，我收到此错误”(已添加强调。)SQL Server 将坚持存储过程与表的定义相匹配，这些表在创建存储过程时存在。如果在创建存储过程时该表不存在，SQL Server 将假定一个匹配表将在运行时出现。

create table t (c int)
go
create procedure p as begin
    drop table t 
    select 1 as diff_column_name into t
    select diff_colun_name from t
end

结果:

Msg 207, Level 16, State 1, Procedure p, Line 6
Invalid column name 'diff_colun_name'.

现在，删除表 t，然后创建过程:

drop table t 
go
create procedure p as begin
    drop table t 
    select 1 as diff_column_name into t
    select diff_colun_name from t
end

Command(s) completed successfully.