使用 SQL Server 中的函数插入/更新/删除

Question

我们可以使用SQL Server Functions执行Insert/Update/Delete语句吗？我已经尝试过，但发生了 SQL Server 错误。

错误：

Invalid use of side-effecting or time-dependent operator in 'DELETE' within a function.

任何人都知道为什么我们不能将Insert/Update/Delete语句与SQL Server函数一起使用。

等待您的好主意

Answer 1

这是使用 sp_executesql 的另一种替代方案（仅在 SQL 2016 中测试）。
正如之前的帖子所指出的，原子性必须在其他地方处理。

CREATE FUNCTION [dbo].[fn_get_service_version_checksum2]
(
    @ServiceId INT
)
RETURNS INT
AS
BEGIN
DECLARE @Checksum INT;
SELECT @Checksum = dbo.fn_get_service_version(@ServiceId);
DECLARE @LatestVersion INT = (SELECT MAX(ServiceVersion) FROM [ServiceVersion] WHERE ServiceId = @ServiceId);
-- Check whether the current version already exists and that it's the latest version.
IF EXISTS(SELECT TOP 1 1 FROM [ServiceVersion] WHERE ServiceId = @ServiceId AND [Checksum] = @Checksum AND ServiceVersion = @LatestVersion)
    RETURN @LatestVersion;
-- Insert the new version to the table.
EXEC sp_executesql N'
INSERT INTO [ServiceVersion] (ServiceId, ServiceVersion, [Checksum], [Timestamp])
VALUES (@ServiceId, @LatestVersion + 1, @Checksum, GETUTCDATE());',
N'@ServiceId INT = NULL, @LatestVersion INT = NULL, @Checksum INT = NULL',
@ServiceId = @ServiceId,
@LatestVersion = @LatestVersion,
@Checksum = @Checksum
;
RETURN @LatestVersion + 1;
END;

Answer 2

不，你不能。

来自 SQL Server 在线书籍：

无法使用用户定义的函数
执行修改操作
数据库状态。

参考。

Answer 3

是的，你可以！））

免责声明：这不是一个解决方案，它更像是一个测试某些东西的黑客。用户定义的函数不能用于执行修改数据库状态的操作。

我找到了一种方法来进行 INSERT、UPDATE 或 DELETE 在函数中使用 xp_cmdshell。

因此，您只需替换 @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

Answer 4

SQL Server 中的函数与数学中的函数一样，不能用于修改数据库。它们是只读的，可以帮助开发人员实现命令查询分离。换句话说，提出问题不应该改变答案。当您的程序需要修改数据库时，请改用存储过程。

Answer 5

您无法像存储过程那样从函数更新表，但可以更新表变量。

例如，您不能在函数中执行此操作：

create table MyTable
(
    ID int,
    column1 varchar(100)
)
update [MyTable]
set column1='My value'

但您可以执行以下操作：

declare @myTable table
(
    ID int,
    column1 varchar(100)
)

Update @myTable
set column1='My value'

Answer 6

是的，可以。

但是，它需要具有 EXTERNAL_ACCESS 或 UNSAFE 权限的 SQL CLR 并指定连接字符串。这显然是不推荐的。

例如，使用 Eval SQL.NET （一个允许在 SQL 中添加 C# 语法的 SQL CLR）

CREATE FUNCTION [dbo].[fn_modify_table_state]
    (
      @conn VARCHAR(8000) ,
      @sql VARCHAR(8000)
    )
RETURNS INT
AS
    BEGIN
        RETURN SQLNET::New('
using(var connection = new SqlConnection(conn))
{
    connection.Open();

    using(var command = new SqlCommand(sql, connection))
    {
        return command.ExecuteNonQuery();
    }
}
').ValueString('conn', @conn).ValueString('sql', @sql).EvalReadAccessInt()

    END

    GO

DECLARE @conn VARCHAR(8000) = 'Data Source=XPS8700;Initial Catalog=SqlServerEval_Debug;Integrated Security=True'
DECLARE @sql VARCHAR(8000) = 'UPDATE [Table_1] SET Value = -1 WHERE Name = ''zzz'''

DECLARE @rowAffecteds INT =  dbo.fn_modify_table_state(@conn, @sql)

文档： 在 SQL 函数中修改表状态

免责声明：我'我是项目的所有者 Eval SQL.NET

Answer 7

您可以将表变量作为返回类型，然后根据该输出更新或插入表。
换句话说，您可以将变量输出设置为原始表，进行修改，然后从函数输出插入到原始表。
这是一个小技巧，但是如果您从原始表中插入@output_table，然后说例如：
插入到my_table
select * from my_function

即可达到结果。

Answer 8

我们不能说他们可能不存在其他方式来在用户定义的函数中执行更新操作。可以肯定的是，在 UDF 中直接 DML 是不可能的。

下面的查询工作正常：

create table testTbl
(
id int identity(1,1) Not null,
name nvarchar(100)
)
GO

insert into testTbl values('ajay'),('amit'),('akhil')
Go

create function tblValued()
returns Table
as
return (select * from testTbl where id = 1)
Go

update tblValued() set name ='ajay sharma' where id = 1
Go

select * from testTbl 
Go

Answer 9

“函数仅具有只读数据库访问权限”
如果函数中允许 DML 操作，那么函数将与存储过程非常相似。

Answer 10

不，您不能执行插入/更新/删除操作。

函数仅适用于 select 语句。并且它只有只读数据库访问权限。

此外：

• 函数每次都会编译。
• 函数必须返回一个值或结果。
• 函数仅适用于输入参数。
• 函数中不使用 Try 和 catch 语句。

Answer 11

  CREATE FUNCTION dbo.UdfGetProductsScrapStatus
(
@ScrapComLevel INT
) 
RETURNS @ResultTable TABLE
( 
ProductName VARCHAR(50), ScrapQty FLOAT, ScrapReasonDef VARCHAR(100), ScrapStatus VARCHAR(50)
) AS BEGIN
        INSERT INTO @ResultTable
            SELECT PR.Name, SUM([ScrappedQty]), SC.Name, NULL
                FROM [Production].[WorkOrder] AS WO
                        INNER JOIN 
                        Production.Product AS PR
                        ON Pr.ProductID = WO.ProductID
                        INNER JOIN Production.ScrapReason AS SC
                        ON SC.ScrapReasonID = WO.ScrapReasonID
                WHERE WO.ScrapReasonID IS NOT NULL
                GROUP BY PR.Name, SC.Name
UPDATE @ResultTable
            SET ScrapStatus = 
            CASE WHEN ScrapQty > @ScrapComLevel THEN 'Critical'
            ELSE 'Normal'
            END
        
RETURN
END

Answer 12

函数并不意味着以这种方式使用，如果您希望执行数据更改，您只需为此创建一个存储过程即可。

Answer 13

如果您需要运行删除/插入/更新，您也可以运行动态语句。 IE：

declare 
    @v_dynDelete                 NVARCHAR(500);

 SET @v_dynDelete = 'DELETE some_table;'; 
 EXEC @v_dynDelete