In this post we are going to Insert data using an UDF.  We all know that User-defined functions (UDF) cannot be used to perform actions that modify the database state.

Error Occur while Inserting using UDF:

Invalid use of the side-affecting operator ‘INSERT’ within a function.

Let’s get it done. First we need to create a database & a table to perform those operations.

Create a new Database:

CREATE TABLE [dbo].[Customer](
	[Id] [int] NOT NULL,
	[Name] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Create a SP:

--Exec Save_Customer 'Shashangka'
ALTER PROCEDURE[dbo].[Save_Customer]
(
	 @CustomerName  NVARCHAR(50)
)
AS
BEGIN
	BEGIN TRY
		 BEGIN TRANSACTION;
			 DECLARE @ReturnValue INT = 0
			 --====================================================----
			 EXEC @ReturnValue = funcSave_customer  @CustomerName
			 SELECT @ReturnValue as ReturnValue
			 --SELECT * FROM [dbo].[Employee]
			 --====================================================----
		 COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION;

		DECLARE @ErrorNumber INT = ERROR_NUMBER();
		DECLARE @ErrorLine INT = ERROR_LINE();
		DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
		DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
		DECLARE @ErrorState INT = ERROR_STATE();
		--print @ErrorMessage; print @ErrorLine
	END CATCH
END

Create UDF: Here we have dynamically build the query

SELECT @sql = '
					INSERT INTO [Customer] (ID, NAME) 
			        VALUES ('+CAST(@id as varchar)+' ,'''+CAST(@CustomerName as nvarchar)+''')
				  ';

then used sp_executesql system stored procedure to execute the statement.

EXECUTE sp_executesql @sql;

Finally the UDF

--exec funcSave_customer ''
ALTER FUNCTION [dbo].[funcSave_customer] 
(
	@CustomerName varchar(50)
)
RETURNS INT
AS
BEGIN
	--SET NOCOUNT ON;
	DECLARE @id INT  
	SET @id = ISNULL(((SELECT MAX(id) FROM Customer)+1),'1')
	DECLARE @sql nvarchar(4000); 
	SELECT @sql = '
					INSERT INTO [Customer] (ID, NAME) 
			        VALUES ('+CAST(@id as varchar)+' ,'''+CAST(@CustomerName as nvarchar)+''')
				  ';
	EXECUTE sp_executesql @sql;  
	RETURN 1
END

Output: