Условие T-SQL IF не работает должным образом

У меня есть хранимая процедура, которая будет ВСТАВЛЯТЬ запись в таблицу. Я создал уникальное ограничение ключа для таблицы, чтобы избежать дублирования записей.

Независимо от того, что определено уникальное ограничение ключа, я использую условие IF() (перед оператором INSERT) для проверки наличия повторяющейся записи, которая может уже существовать.

Однако условный оператор, который я использую для проверки дублирующейся записи, по-видимому, не влияет на то, выполняется ли INSERT. - т. е. когда повторяющаяся запись отправляется в sproc, возникает исключение «Нарушение ограничения UNIQUE KEY...».

Вот пример моего sproc:

BEGIN       
    if
        (SELECT Count(f1)
        FROM table
        WHERE f1 = @f1
        AND f2= @f2) 
    <= 0
    BEGIN
        INSERT INTO table
        (f1,f2)
        VALUES
        (@f1, @f2)

        RETURN @@IDENTITY
    END
END

Что-то не так с моим синтаксисом? Или, может быть, я иду об этом неправильно?


sql if-statement sql-server tsql sql-server-2008
person Jed    schedule 01.10.2010    source источник
comment
Это при высокой нагрузке? Две параллельные транзакции могут прочитать, что строка не существует, а затем перейти к этапу вставки. Связанный вопрос Также не относится к вашему вопросу, но вы следует использовать scope_identity, если цель состоит в том, чтобы вернуть идентификатор самой последней вставленной строки.   -  person Martin Smith    schedule 02.10.2010

Ответы (4)


arrow_upward
3
arrow_downward

Если вы используете SQL Server 2008 или новее, вам следует использовать MERGE заявление:

MERGE INTO table AS t
USING (VALUES (@f1, @f2)) AS s (f1, f2)
    ON s.f1 = t.f1
        AND s.f2 = t.f2
WHEN NOT MATCHED BY t THEN
    INSERT (f1, f2) VALUES (@f1, @f2)
person LukeH    schedule 01.10.2010
comment
Emtucifor: Какая проблема параллелизма у MERGE? - person Gabe; 02.10.2010

arrow_upward
3
arrow_downward

Count(field) подсчитывает ненулевые значения.
Это может отличаться от count(*), который подсчитывает строки.

Но я бы не советовал использовать здесь count.
Взгляните на этот вопрос.

person GSerg    schedule 01.10.2010

arrow_upward
2
arrow_downward

Это более безопасно для параллелизма:

INSERT INTO table
SELECT @f1, @f2, @f3
  FROM TABLE WITH (UPDLOCK, HOLDLOCK)
 WHERE NOT EXISTS(SELECT NULL
                    FROM table
                   WHERE f1 = @f1
                     AND f2 = @f2
                     AND f3 = @f3)

Или, если вы хотите сохранить логику принятия решения (но менее защищенную от параллелизма), используйте НЕ СУЩЕСТВУЕТ:

IF NOT EXISTS(SELECT NULL
                FROM table
               WHERE f1 = @f1
                 AND f2 = @f2
                 AND f3 = @f3) 
BEGIN

    INSERT INTO table
      (f1,f2, f3)
    VALUES
      (@f1, @f2, @f3)

    RETURN @@SCOPE_IDENTITY

END
person OMG Ponies    schedule 01.10.2010
comment
Вы должны использовать SCOPE_IDENTITY() вместо @@IDENTITY, триггер в таблице может изменить @@IDENTITY - person Jason Goemaat; 02.10.2010
comment
Тем не менее, он по-прежнему НЕ безопасен для параллелизма. Вы должны либо использовать WITH (UPDLOCK, HOLDLOCK) в выборе, либо восстановиться после неудачной вставки и вместо этого выполнить обновление. - person ErikE; 02.10.2010

arrow_upward
1
arrow_downward

Спасибо за ваш вклад OMG/Martin, но оказалось, что корень моей проблемы был из-за значения параметра, которое было нулевым.

Конкретно:

--sproc params
@f1 int,
@f2 nvarchar(30),
@f3 datetime = null --<<a null value will screw up the IF() condition

IF(SELECT COUNT(f1)
FROM table
WHERE f1 = @f1
AND f2 = @f2
AND f3 = @f3)
<1

BEGIN
  INSERT INTO....
END

Обратите внимание, что значение параметра @f3 по умолчанию равно null. Итак, в случае, когда вызывающая сторона sproc не передает параметр @f3, условие IF() вернет 0 (ноль), даже если есть соответствующие @f1, @f2 и null @f3.

Например: скажем, в таблице уже есть запись

f1   f2   f3
--------------
45   foo  NULL

Теперь вызывающий объект запускает sproc, отправляя его:

@f1=45

@f2=фу

(Обратите внимание, что вызывающий не указывает @f3)

Когда условие IF() сработает, оно вернет 0 (ноль). Странно, а? Интуитивно я бы подумал, что условие вернет 1, поскольку параметры точно совпадают с существующими значениями в таблице.

Чтобы сделать ситуацию немного более запутанной (для меня, во всяком случае), это то, что даже если IF () завершается успешно (возвращает ноль), я получаю исключение «Нарушение ограничения UNIQUE KEY..». На самом деле, я не удивлен, что возникает исключение, поскольку я считаю, что значения параметров идеально соответствуют существующей записи в таблице - для меня запутанная часть заключается в том, почему условие IF() противоречит исключению нарушения .

Кстати, OMGPonies, я попробовал использовать ваше предложение (СУЩЕСТВУЕТ), и возникают те же симптомы. По-видимому, фактор NULL делает вещи странными.

person Jed    schedule 01.10.2010
comment
Если бы вы показали нам свой фактический код, я бы догадался, что это вариант! Использование Merge и Scope_Identity по-прежнему безопаснее, чем то, что вы делаете сейчас. - person Martin Smith; 02.10.2010
comment
Кстати, решение MERGE не решает проблему NULL. Другими словами, в соединении ON моего оператора MERGE, если одно (или несколько) полей имеют значение NULL, всегда выполняется WHEN NOT MATCHED. - person Jed; 02.10.2010
comment
Конечно, не будет, потому что это была логическая ошибка вашего конца. Никакая языковая функция не исправит это. Слияние будет безопаснее в условиях параллелизма. - person Martin Smith; 02.10.2010
comment
Вы говорите, что невозможно искать нулевые записи? - person Jed; 02.10.2010