Недопустимый оператор побочного действия. Вставьте внутри функции

У меня есть следующий код в моей sql-функции:

if @max_chi > -999
begin
    INSERT INTO CH_TABLE(X1, X2, VALUE)
    VALUES(cur_out.sessionnumber, maxpos, max_chi)

    commit
end

Ниже приведен запрос SQL Server 2008 и он дает мне ошибку:

Недопустимое использование оператора-оператора "INSERT" в функции.

Почему мне не разрешают это делать? Что я могу сделать, чтобы исправить это?

Ответы

Ответ 1

Вы не можете использовать функцию для вставки данных в базовую таблицу. Функции возвращают данные. Это указано как самое первое ограничение в документации:

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

"Изменить состояние базы данных" включает в себя изменение любых данных в базе данных (хотя переменная таблицы является очевидным исключением, о котором OP не беспокоило бы около 3 лет назад - эта переменная таблицы живет только на время вызова функции и не влияет на базовые таблицы каким-либо образом).

Вы должны использовать хранимую процедуру, а не функцию.

Ответ 2

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

Ответ 3

Я нашел один способ сделать вставку или обновление, поэтому вам нужно просто заменить код внутри переменной @sql.

CREATE FUNCTION [dbo].[_tmp_func](@orderID NVARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @sql varchar(4000), @cmd varchar(4000)
SELECT @sql = 'INSERT INTO _ord (ord_Code) VALUES (''' + @orderID + ''') '
SELECT @cmd = 'sqlcmd -S ' + @@servername +
              ' -d ' + db_name() + ' -Q "' + @sql + '"'
EXEC master..xp_cmdshell @cmd, 'no_output'
RETURN 1
END

Ответ 4

Есть исключение (я использую SQL 2014), когда вы используете только Insert/Update/Delete на Declared-Tables. Эти операторы Insert/Update/Delete не могут содержать оператор OUTPUT. Другое ограничение состоит в том, что вам не разрешено делать MERGE, даже в объявленную таблицу. Я распаковал свои операторы Merge, которые не сработали, в операторы Insert/Update/Delete, которые действительно работали.

Причина, по которой я не преобразовал ее в хранимую процедуру, заключается в том, что функция table была быстрее (даже без MERGE), чем хранимая процедура. Это несмотря на хранимую процедуру, позволяющую мне использовать Temp-Таблицы с статистикой. Мне нужно, чтобы функция table была очень быстрой, так как она называется 20-K раз/день. Эта функция таблицы никогда не обновляет базу данных.

Я также заметил, что функции NewId() и RAND() SQL не разрешены в функции.