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

Can we perform Insert/Update/Delete statement with SQL Server Functions. I have tried with but SQL Server error is occured.

可以使用SQL Server函数执行插入/更新/删除语句吗?我试过了，但是SQL Server错误发生了。

Error:

错误:

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

AnyBody have any Idea why we can not use Insert/Update/Delete statements with SQL Server functions.

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

Waiting for your good idea's

等待你的好主意

8 个解决方案

#1

No, you cannot.

不,你不能。

From SQL Server Books Online:

从SQL Server Books联机:

User-defined functions cannot be used to perform actions that modify the database state.

用户定义的函数不能用于执行修改数据库状态的操作。

Ref.

Ref。

#2

Functions in SQL Server, as in mathematics, can not be used to modify the database. They are intended to be read only and can help developer to implement command-query separation. In other words, asking a question should not change the answer. When your program needs to modify the database use a stored procedure instead.

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

#3

Yes, you can!))

是的,你可以!)

I found one way to make INSERT, UPDATE or DELETE in function using xp_cmdshell.

我找到了一种使用xp_cmdshell在函数中进行插入、更新或删除的方法。

So you need just to replace the code inside @sql variable.

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

You can't update tables from a function like you would a stored procedure, but you CAN update table variables.

不能像存储过程那样从函数中更新表，但可以更新表变量。

So for example, you can't do this in your function:

例如，你不能在函数中这样做

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

but you can do:

但是你能做什么:

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

Update @myTable
set column1='My value'

#5

Yes, you can.

是的,你可以。

However it's require SQL CLR with EXTERNAL_ACCESS or UNSAFE permission and specifying a connection string. This is obviously not recommended.

但是，它需要具有EXTERNAL_ACCESS或不安全权限并指定连接字符串的SQL CLR。显然不建议这样做。

By example, using Eval SQL.NET (a SQL CLR which allow to add C# syntax in SQL)

例如，使用Eval SQL。NET(一个SQL CLR，允许在SQL中添加c#语法)

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)

Documentation: Modify table state within a SQL Function

文档:修改SQL函数中的表状态

Disclaimer: I'm the owner of the project Eval SQL.NET

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

#6

Just another alternative using sp_executesql (tested only in SQL 2016). As previous posts noticed, atomicity must be handled elsewhere.

只是使用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;

#7

We can't say that it is possible of not their is some other way exist to perform update operation in user-defined Function. Directly DML is not possible in UDF it is for sure.

我们不能说在用户定义函数中执行更新操作是不可能的。直接DML在UDF中是不可能的，这是肯定的。

Below Query is working perfectly:

下面的查询工作非常完美:

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

#8

-1

if you need to run the delete/insert/update you could also run dynamic statements. i.e.:

如果需要运行delete/insert/update，还可以运行动态语句。例如:

declare 
    @v_dynDelete                 NVARCHAR(500);

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

#1