SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[rolePrivileges](
	[roleId] [int] NULL,
	[roleFields] [varchar](500) NULL
) ON [PRIMARY]
INSERT [dbo].[rolePrivileges] ([roleId], [roleFields]) VALUES (1, N'dataField1,dataField2,dataField3,dataField4')
INSERT [dbo].[rolePrivileges] ([roleId], [roleFields]) VALUES (2, N'dataField1,dataField2')
INSERT [dbo].[rolePrivileges] ([roleId], [roleFields]) VALUES (3, N'dataField1,dataField3,dataField4')

CREATE TABLE [dbo].[myTable](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[dataField1] [varchar](20) NULL,
	[dataField2] [varchar](20) NULL,
	[dataField3] [varchar](20) NULL,
	[dataField4] [varchar](20) NULL,
	[userId] [int] NULL,
	[recTime] [datetime] NULL,
	[recUniq] [uniqueidentifier] NULL,
 CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[myTable] ADD  CONSTRAINT [DF_myTable_recTime]  DEFAULT (getdate()) FOR [recTime]
ALTER TABLE [dbo].[myTable] ADD  CONSTRAINT [DF_myTable_recUniq]  DEFAULT (newid()) FOR [recUniq]
INSERT INTO [dbo].[myTable]([dataField1],[dataField2],[dataField3],[dataField4],[userId])
VALUES ('AA','BB','CC','DD',1),('AA','BB','CC','DD',1),('AA','BB','CC','DD',1)
6 rows affected

select * from rolePrivileges
select * from myTable


roleId
roleFields


1
dataField1,dataField2,dataField3,dataField4

2
dataField1,dataField2

3
dataField1,dataField3,dataField4




id
dataField1
dataField2
dataField3
dataField4
userId
recTime
recUniq


1
AA
BB
CC
DD
1
2022-10-25 16:04:20.930
b729be62-f16d-4282-a9b9-fcac9eef6d4e

2
AA
BB
CC
DD
1
2022-10-25 16:04:20.930
0b655bb6-2462-45ac-b741-e64a39a9ede1

3
AA
BB
CC
DD
1
2022-10-25 16:04:20.930
bf3e129b-327c-418e-b942-0fe7f23322a4


create PROCEDURE myTableProcedure2 
     @id int=0
    ,@dataField1 varchar(20) = '' 
    ,@dataField2 varchar(20) = ''
    ,@dataField3 varchar(20) = ''
    ,@dataField4 varchar(20) = ''
    ,@userId int=0
    ,@role tinyint=0 --1=All Column, 2=dataField1,dataField2, 3=dataField1,dataField3,dataField4 (not=2)
AS

SET NOCOUNT ON;

IF (@id = 0)
BEGIN
    INSERT myTable (dataField1, dataField2, dataField3, dataField4, userId)
    VALUES (@dataField1, @dataField2, @dataField3, @dataField4, @userId);
END;
ELSE
BEGIN


declare @sql nvarchar(max)=(select
concat(upd,sp,clm,whr)

from (
SELECT 
  (case when value=first_value(value) over(partition by roleId order by roleId asc) then 'update myTable set ' end) as 'upd',
  (case when value!=first_value(value) over(partition by roleId order by roleId asc) then ', ' end) as 'sp',
  concat(value,'=@',value) as 'clm',
  (case when value=last_value(value) over(partition by roleId order by roleId asc) then concat('  where id=@id','') end) as 'whr'
FROM 
  rolePrivileges CROSS APPLY STRING_SPLIT(roleFields, ',')
Where
  roleId=@role
) sqlStr

for xml path('')
)

print @sql

EXEC sp_executesql @sql,
      N'@id int ,@dataField1 varchar(20), @dataField2 varchar(20), @dataField3 varchar(20), @dataField4 varchar(20), @userId int',
     @id = @id
    ,@dataField1 = @dataField1
    ,@dataField2 = @dataField2
    ,@dataField3 = @dataField3
    ,@dataField4 = @dataField4
    ,@userId = @userId;

END;
execute myTableProcedure2      @id=1    ,@dataField1= 'NewValueRole1'     ,@dataField2= 'NewValueRole1'    ,@dataField3= 'NewValueRole1'    ,@dataField4= 'NewValueRole1'    ,@userId=1    
  ,@role=1
select * from myTable WHERE id=1

execute myTableProcedure2      @id=2    ,@dataField1= 'NewValueRole2'     ,@dataField2= 'NewValueRole2'    ,@dataField3= 'NewValueRole2'    ,@dataField4= 'NewValueRole2'    ,@userId=1    
  ,@role=2
select * from myTable WHERE id=2


execute myTableProcedure2      @id=3    ,@dataField1= 'NewValueRole3'     ,@dataField2= 'NewValueRole3'    ,@dataField3= 'NewValueRole3'    ,@dataField4= 'NewValueRole3'    ,@userId=1    
  ,@role=3
select * from myTable WHERE id=3


id
dataField1
dataField2
dataField3
dataField4
userId
recTime
recUniq


1
NewValueRole1
NewValueRole1
NewValueRole1
NewValueRole1
1
2022-10-25 16:04:20.930
b729be62-f16d-4282-a9b9-fcac9eef6d4e


update myTable set dataField1=@dataField1, dataField2=@dataField2, dataField3=@dataField3, dataField4=@dataField4  where id=@id




id
dataField1
dataField2
dataField3
dataField4
userId
recTime
recUniq


2
NewValueRole2
NewValueRole2
CC
DD
1
2022-10-25 16:04:20.930
0b655bb6-2462-45ac-b741-e64a39a9ede1


update myTable set dataField1=@dataField1, dataField2=@dataField2  where id=@id




id
dataField1
dataField2
dataField3
dataField4
userId
recTime
recUniq


3
NewValueRole3
BB
NewValueRole3
NewValueRole3
1
2022-10-25 16:04:20.930
bf3e129b-327c-418e-b942-0fe7f23322a4


update myTable set dataField1=@dataField1, dataField3=@dataField3, dataField4=@dataField4  where id=@id
roleId	roleFields
1	dataField1,dataField2,dataField3,dataField4
2	dataField1,dataField2
3	dataField1,dataField3,dataField4
id	dataField1	dataField2	dataField3	dataField4	userId	recTime	recUniq
1	AA	BB	CC	DD	1	2022-10-25 16:04:20.930	b729be62-f16d-4282-a9b9-fcac9eef6d4e
2	AA	BB	CC	DD	1	2022-10-25 16:04:20.930	0b655bb6-2462-45ac-b741-e64a39a9ede1
3	AA	BB	CC	DD	1	2022-10-25 16:04:20.930	bf3e129b-327c-418e-b942-0fe7f23322a4