Удаление таблицы в хранимой процедуре не работает должным образом?

Question

У меня есть хранимая процедура, которая удаляет таблицу, если она существует, затем воссоздает таблицу и заполняет ее соответствующими данными, у моего друга примерно такой же код, единственное реальное отличие заключается в заголовках столбцов для таблицы.

В качестве иллюстрации, вот как выглядит мой (не совсем, просто представление).

+----+-----+-----+--------+
| 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 после использования другого я получаю эту ошибку:

Сообщение 207, Уровень 16, Состояние 1, Процедура XYZ, Строка 59
Неверное имя столбца 'Foo'.

Сообщение 213, Уровень 16, Состояние 1, Процедура XYZ, Строка 61
Имя столбца или количество предоставленных значений не соответствует определению таблицы.

Тем не менее, в начале обеих хранимых процедур, у нас есть это:

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

Если вы можете использовать другое имя таблицы, начните с этого. И если таблица должна существовать только на мгновение после выполнения процедуры, чтобы ее можно было выбрать, то создайте глобальную временную таблицу (т. Е. Имя таблицы начинается с ## как в ##MyTable).

Однако, если необходимо использовать то же имя таблицы, что и у вашего одноклассника, учитель, вероятно, пытается заставить вас узнать об отложенном разрешении объекта (т.е. ответе @ Шеннона) и о том, как его обойти, потому что за пределами изучения этого Сценарий не имеет смысла, поскольку в реальности никогда бы не поступили так.

Подпроцессы (т.е. EXEC а также sp_executesql) не разрешаются немедленно, так как они не выполняются при создании хранимой процедуры. Итак, проще говоря, просто объявите новую переменную NVARCHAR(MAX) для хранения динамического SQL и поместите SELECT заявление там. использование sp_executesql пройти в @year переменная. Вы создаете реальную таблицу, чтобы она сохранялась после окончания подпроцесса, а затем ALTER TABLE Заявление будет работать.

Дополнительные примечания:

• Вам не нужно ALTER инструкция для установки типа данных поля [Richting]. Просто скажите SQL Server, какой тип указан в вашем операторе SELECT:

  CONVERT(NVARCHAR(8), '') AS [Richting]
  

• Вы действительно не хотите делать CONVERT(NVARCHAR(8), OrderDate, 112) сравнивать со значением, поскольку оно делает недействительным использование любых индексов, которые могут быть включены [OrderDate], Вместо этого создайте значение даты из строк и преобразуйте его в DATETIME или DATE (т.е. CONVERT(DATETIME, @year + '0101')).

  Чтобы лучше понять эту проблему, прочитайте Sargability: почему%string% является медленным, и, по крайней мере, первая ссылка внизу, а именно: Что делает оператор SQL саргируемым?

• Вы действительно не хотите преобразовывать поле OrderDate в NVARCHAR(4) просто для сравнения года, по той же причине, что только что упоминалась в приведенном выше пункте. По крайней мере, используя YEAR() Функция была бы более прямой. Но если вы хотите убедиться, что индексы могут быть использованы, вы не можете поместить функцию в поле. Но вы хотите только год. Так что год не такой же, как BETWEEN @Year + '0101' AND @Year + '1231'?;-)

  Интересно, что первый пример принятого ответа в "Что делает SQL-оператор саргисным?" ТАК вопрос, связанный с предыдущим пунктом, это то, что я рекомендую здесь:).

Answer 2

"Однако при создании 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.

Answer 3

Насколько я понимаю, неправильные запросы - это вставки, потому что движок не может найти правильную структуру таблицы, проверьте, имеет ли вставки ту же структуру, что и во втором примере таблицы. Не забудьте проверить ИСПОЛЬЗОВАНИЕ в начале скрипта, возможно, вы используете другую базу данных, это может произойти:).

Answer 4

В последнем фрагменте кода у вас есть

AND xtype = 'U'

Если ваша сортировка чувствительна к регистру, падение не происходит и, следовательно, ошибка.