Insert/Update/Delete with function in SQL Server

Question!

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

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.

Waiting for your good idea's



Answers

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

declare 
    @v_dynDelete                 NVARCHAR(500);

 SET @v_dynDelete = 'DELETE some_table;'; 
 EXEC @v_dynDelete
By : AnilaK


Yes, you can!))

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

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

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


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.

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

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

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



This video can help you solving your question :)
By: admin