can we use DML commands in sql functions?

Question

Im curious to know can we use DML commands like insert/update/delete in functions in sql server.

Answer 1

In Micosoft SQL Server, functions cannot produce side-effects. This means you cannot modify the database state (change data available outside the function scope) within the function although you can execute SELECT DML. Below is an example script that illustrates this.

CREATE TABLE dbo.Test(SomeData int IDENTITY);
GO
CREATE FUNCTION dbo.BadFunction(@SomeData int)
RETURNS int
AS
BEGIN
    INSERT INTO dbo.Test (SomeData) VALUES(@SomeData);
    RETURN SCOPE_IDENTITY();
END;
GO

The function will not be created due to this error:

Msg 443, Level 16, State 15, Procedure BadFunction, Line 6 [Batch Start Line 4] Invalid use of a side-effecting operator 'INSERT' within a function.

The function create will succeed if one uses a table variable instead of a regular table because the scope of the table variable is limited to within the function:

CREATE FUNCTION dbo.GoodFunction(@SomeData int)
RETURNS int
AS
BEGIN
    DECLARE @TableVariable TABLE(ID int IDENTITY, SomeData int);
    INSERT INTO @TableVariable (SomeData) VALUES(@SomeData);
    RETURN SCOPE_IDENTITY();
END;
GO