Creating a variable procedure statement

Question

I have an procedure statement as below. My update sentence changes according to the @role parameter here.

Is it possible to dynamically make the sentence in the procedure according to the "rolePrivileges" table?

CREATE TABLE [dbo].[myTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [dataField1] [varchar](10) NULL,
    [dataField2] [varchar](10) NULL,
    [dataField3] [varchar](10) NULL,
    [dataField4] [varchar](10) 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]

create PROCEDURE myTableProcedure 
     @id int=0
    ,@dataField1 varchar(10) = '' 
    ,@dataField2 varchar(10) = ''
    ,@dataField3 varchar(10) = ''
    ,@dataField4 varchar(10) = ''
    ,@userId int=0
    ,@role tinyint=0 --1=All Column, 2=dataField1,dataField2, 3=dataField1,dataField3,dataField4 (not=2)
AS
BEGIN
    SET NOCOUNT ON

    --New Record
    if (@id=0)
    Begin
    insert into myTable (dataField1,dataField2,dataField3,dataField4,userId) values (@dataField1,@dataField2,@dataField3,@dataField4,@userId)
    End 
    --Updated
    else
    Begin
---------------------------- Variable partition based on @role value.
        if (@role=1)
        begin
            update myTable set
                 dataField1=@dataField1
                ,dataField2=@dataField2
                ,dataField3=@dataField3
                ,dataField4=@dataField4
                ,recTime=getdate()
            where id=@id
        end
        else if (@role=2)
        begin
            update myTable set
                 dataField1=@dataField1
                ,dataField2=@dataField2
                --,dataField3=@dataField3
                --,dataField4=@dataField4
                ,recTime=getdate()
            where id=@id
        end
        else if (@role=3)
        begin
            update myTable set
                 dataField1=@dataField1
                --,dataField2=@dataField2
                ,dataField3=@dataField3
                ,dataField4=@dataField4
                ,recTime=getdate()
            where id=@id
        end
---------------------------- Variable partition based on @role value./
    End
END

--role=1 insert
execute myTableProcedure 
     @id=0
    ,@dataField1= 'A' 
    ,@dataField2= 'B'
    ,@dataField3= 'C'
    ,@dataField4= 'D'
    ,@userId=789
    ,@role=1
--role=2 insert
execute myTableProcedure 
     @id=0
    ,@dataField1= 'A' 
    ,@dataField2= 'B'
    ,@dataField3= 'C'
    ,@dataField4= 'D'
    ,@userId=123
    ,@role=2
--role=3 insert
execute myTableProcedure 
     @id=0
    ,@dataField1= 'A' 
    ,@dataField2= 'B'
    ,@dataField3= 'C'
    ,@dataField4= 'D'
    ,@userId=456
    ,@role=3

select * from myTable

id	dataField1	dataField2	dataField3	dataField4	userId	recTime	recUniq
1	A	B	C	D	789	2022-10-25 13:22:28.380	63e3356b-0a28-4de5-9441-c7be13cd03b3
2	A	B	C	D	123	2022-10-25 13:22:28.380	657e8971-a6a3-4ab2-9849-ee7060324565
3	A	B	C	D	456	2022-10-25 13:22:28.380	b1f2e13d-bcc3-4e76-b6a8-05c3a43da619

--role=1 update
execute myTableProcedure 
     @id=1
    ,@dataField1= '11' 
    ,@dataField2= '11'
    ,@dataField3= '11'
    ,@dataField4= '11'
    ,@userId=789
    ,@role=1
--role=2 update
execute myTableProcedure 
     @id=2
    ,@dataField1= '22' 
    ,@dataField2= '22'
    ,@dataField3= '22'
    ,@dataField4= '22'
    ,@userId=123
    ,@role=2
--role=3 update
execute myTableProcedure 
     @id=3
    ,@dataField1= '33' 
    ,@dataField2= '33'
    ,@dataField3= '33'
    ,@dataField4= '33'
    ,@userId=456
    ,@role=3

select * from myTable

id	dataField1	dataField2	dataField3	dataField4	userId	recTime	recUniq
1	11	11	11	11	789	2022-10-25 13:22:28.380	63e3356b-0a28-4de5-9441-c7be13cd03b3
2	22	22	C	D	123	2022-10-25 13:22:28.393	657e8971-a6a3-4ab2-9849-ee7060324565
3	33	B	33	33	456	2022-10-25 13:22:28.393	b1f2e13d-bcc3-4e76-b6a8-05c3a43da619

fiddle

Answer 1

I'm not entirely sure what issue you are having with executing a dynamic statement, but this is roughy how you would do it.

Just use a bunch of CASE expressions to build up the statement dynamically. Then pass the parameters in using sp_executesql. Do not inject the data directly into the dynamic part.

CREATE OR ALTER PROCEDURE myTableProcedure 
     @id int=0
    ,@dataField1 varchar(10) = '' 
    ,@dataField2 varchar(10) = ''
    ,@dataField3 varchar(10) = ''
    ,@dataField4 varchar(10) = ''
    ,@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) = CONCAT('
update myTable
set
  dataField1 = @dataField1
  ,',
      CASE WHEN @role IN (1, 2) THEN 'dataField2 = @dataField2
  ,' END,
      CASE WHEN @role IN (1, 3) THEN 'dataField3 = @dataField3
  ,dataField4 = @dataField4
  ,' END,
      'recTime = getdate()
where id = @id;
'
    );

    PRINT @sql;     -- your friend

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