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 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]
(
           NVARCHAR(50)
)
AS
BEGIN
        BEGIN TRY
                 BEGIN TRANSACTION;
                         DECLARE  INT = 0
                         --====================================================----
                         EXEC  = funcSave_customer  
                         SELECT  as ReturnValue
                         --SELECT * FROM [dbo].[Employee]
                         --====================================================----
                 COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
                IF @ > 0
                ROLLBACK TRANSACTION;

                DECLARE  INT = ERROR_NUMBER();
                DECLARE  INT = ERROR_LINE();
                DECLARE  NVARCHAR(4000) = ERROR_MESSAGE();
                DECLARE  INT = ERROR_SEVERITY();
                DECLARE  INT = ERROR_STATE();
                --print ; print 
        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( as nvarchar)+''')
                                  ';

then used  system stored procedure to execute the statement.

EXECUTE sp_executesql @sql;

Finally the UDF

--exec funcSave_customer ''
ALTER FUNCTION [dbo].[funcSave_customer] 
(
         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( as nvarchar)+''')
                                  ';
        EXECUTE sp_executesql @sql;  
        RETURN 1
END

Output: