Почему использование инструкции INSERT - EXEC [хранимая процедура] в пользовательской функции вызывает побочную ошибку?

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

Вызов EXEC для хранимой процедуры работает нормально: http://sqlfiddle.com/#!3/ed11a/16

Так почему же вставка результатов EXEC в табличную переменную вызывает ошибку: http://sqlfiddle.com/#!3/ed11a/18

Я получаю сообщение об ошибке: Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.

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


sql exec user-defined-functions sql-server-2005 table-variable
person Erica    schedule 17.07.2012    source источник

Ответы (1)


arrow_upward
3
arrow_downward

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

Alter function dbo.MyTable() 
RETURNS  @table TABLE 
(
    -- columns returned by the function
    Col1 int null, 
    Col2 int null, 
    Col3 int null,
    Col4 int null,
    Col5 int null, 
    Col6 int null

)
as
Begin
INSERT INTO @table Values( null, null, 5, null, 63, null)

return 
End
person cmsjr    schedule 17.07.2012
comment
Но разве это не было бы верно даже без инструкции INSERT? - person Erica; 17.07.2012
comment
Да, вы получаете сообщение об ошибке из-за оператора Exec, вы не можете вызывать какую-либо хранимую процедуру из пользовательской функции. - person cmsjr; 17.07.2012
comment
Меня больше интересовало, почему он позволяет мне создавать (не запускать) функцию с помощью EXEC, но не с помощью INSERT-EXEC. Оказывается, он разрешает вызовы расширенных хранимых процедур (msdn.microsoft.com/ en-us/library/ms191007#ValidStatements), поэтому я предполагаю, что в момент создания он не знает, что это за SP, но если вы используете его со вставкой, он знает, что может. не является расширенным SP и терпит неудачу. Что касается вашего SQL, то я не пытаюсь вернуть таблицу - она ​​мне просто нужна для обработки. Кроме того, я не думаю, что вам нужна ваша временная таблица - вы можете вставить ее прямо в переменную вашей таблицы. Вместо этого я буду использовать SP. Спасибо! - person Erica; 17.07.2012
comment
Вы правы, мне не нужна была временная таблица. Я обновил его для потомков. На самом деле я не знал, что вы можете выполнять хранимые процедуры в функциях, хорошая находка. - person cmsjr; 17.07.2012