[sql-server] Insert/Update/Delete with function in SQL Server

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

This question is related to sql-server sql-server-2008 sql-server-2012

The answer is


"Functions have only READ-ONLY Database Access" If DML operations would be allowed in functions then function would be prety similar to stored Procedure.


You can have a table variable as a return type and then update or insert on a table based on that output. In other words, you can set the variable output as the original table, make the modifications and then do an insert to the original table from function output. It is a little hack but if you insert the @output_table from the original table and then say for example: Insert into my_table select * from my_function

then you can achieve the result.


Yes, you can!))

Disclaimer: This is not a solution, it is more of a hack to test out something. User-defined functions cannot be used to perform actions that modify the database state.

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

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.


Functions are not meant to be used that way, if you wish to perform data change you can just create a Stored Proc for that.


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

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;

No, you can not do Insert/Update/Delete.

Functions only work with select statements. And it has only READ-ONLY Database Access.

In addition:

  • Functions compile every time.
  • Functions must return a value or result.
  • Functions only work with input parameters.
  • Try and catch statements are not used in functions.

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.

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

  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

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

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'

Yes, you can.

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

For 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


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?

Examples related to sql-server-2012

Count the Number of Tables in a SQL Server Database SQL Server IF EXISTS THEN 1 ELSE 2 Get last 30 day records from today date in SQL Server No process is on the other end of the pipe (SQL Server 2012) How to subtract 30 days from the current date using SQL Server Possible to restore a backup of SQL Server 2014 on SQL Server 2012? SQL Server: Best way to concatenate multiple columns? SQL Server - An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN' SSIS Excel Connection Manager failed to Connect to the Source Sql server - log is full due to ACTIVE_TRANSACTION