-1

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?

rolePrivileges

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

5
  • I'm going to go with a big no on this design - can you highlight exactly what you're trying to do and we can start there? Also, create a new question asking the same thing as your old question dba.stackexchange.com/q/318413/212533
    – bbaird
    Oct 25 at 13:33
  • The question is not the same. This question is about whether the sentence in the transaction can be dynamic. The other question is about column-based authorization.
    – omerix
    Oct 25 at 13:57
  • Then please explain more clearly exactly what you want to achieve. You want to dynamically create and execute the update statement? What have you tried so far? And why does it need to be dynamic anyway? Oct 25 at 14:00
  • @Charlieface Yes. I want to dynamically generate and execute the update statement. This is how I dynamically created the sentence from the table. fiddle But how do I put it in the procedure. I do not know.
    – omerix
    Oct 25 at 14:36
  • If you're passively dropping data, it's not going to go well. This is a terrible design that will in fact lose data. Oct 26 at 13:15

1 Answer 1

2

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;
1
  • Got what I wanted with a little update. Thank you. fiddle
    – omerix
    Oct 25 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.