How to create user defined function for below scenario?

Hi.

First of all, you can't do any insert/update/delete opertions directly. For that you need to use dynamic sql and to run that you need to use sp_executesql.

The complete function is here. So you need to change the table name and column name .

CREATE FUNCTION [dbo].[fn_no_of_work_period]
(
@SalFreq VARCHAR(100),
@No_of_work_period INT
)
RETURNS INT
AS
BEGIN
IF @SalFreq = 'Monthly'
BEGIN
EXEC sp_executesql N'
INSERT INTO [NoOfWorkTable] (No_of_work_period)
VALUES (@Noofworkperiod);',
N'@Noofworkperiod INT = NULL',
@Noofworkperiod = @No_of_work_period;
END
ELSE
BEGIN
EXEC sp_executesql N'
INSERT INTO [NoOfWorkTable] (No_of_work_period)
VALUES (@Noofworkperiod);',
N'@Noofworkperiod INT = NULL',
@Noofworkperiod = @No_of_work_period;
END
RETURN 0
END;

Thanks.