SQL Server - Invalid Use of a Side-Effecting Operator 'insert' within a Function

Generally this problem will occur whenever we try to write insert, update and delete queries inside of functions like as shown below

CREATE FUNCTION [dbo].[testfunction]

(

@ChannelID VARCHAR(50),

@UserId INT

)

RETURNS INT

AS

BEGIN

DECLARE @Channel INT

Set @Channel  = 0

INSERT INTO LeadChannel(Source,Createdby)

VALUES(@ChannelID,@UserId)

SELECT @Channel= @@IDENTITY

RETURN @Channel

END

In above function I written insert query because of that when we call this function we will get error like “invalid use of a side-effecting operator 'insert' within a function.”

To solve this problem we should not use insert, update and delete queries inside of functions.


I hope it helps you to solve your problem.