我的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"。
为什么不允许我这样做?我能做些什么来解决这个问题?
不能使用函数将数据插入基表。函数返回数据。这在文档中被列为第一个限制:
用户定义的函数不能用于执行修改数据库状态的操作。
"修改数据库状态"包括更改数据库中的任何数据(尽管表变量是一个明显的例外,OP在3年前不会关心它——这个表变量只在函数调用期间有效,不会以任何方式影响底层表)。
您应该使用存储过程,而不是函数。
免责声明:这不是一个解决方案,它更像是一个测试东西的破解。用户定义的函数不能用于执行修改数据库状态的操作
我找到了一种使用sqlcmd.exe进行插入或更新的方法,因此您只需要替换@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
函数不能用于修改基表信息,请使用存储过程。
当您仅在声明表上使用Insert/Update/Delete时,会出现一个异常(我使用的是SQL 2014)。这些Insert/Update/Delete语句不能包含OUTPUT语句。另一个限制是不允许执行MERGE,甚至不允许对声明表执行MERGE。我将不起作用的Merge语句分解为起作用的Insert/Update/Delete语句。
我没有将其转换为存储过程的原因是表函数比存储过程更快(即使没有MERGE)。尽管存储过程允许我使用具有统计信息的临时表,但情况依然如此。我需要table函数非常快,因为它每天被调用20-K次。此表函数从不更新数据库。
我还注意到,函数中不允许使用NewId()和RAND()SQL函数。