主页 / dba / 问题 / 318701
Accepted
omerix
Asked: 2022-10-26 05:24:02 +0800 CST

创建可变过程语句

  • 772

我有一个如下的程序语句。我的更新语句根据此处的@role 参数而变化。

是否可以根据“rolePrivileges”表在程序中动态造句？

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 数据字段1 数据字段2 数据字段3 数据字段4 用户身份 记录时间 recUniq
1 一个 乙 C D 789 2022-10-25 13:22:28.380 63e3356b-0a28-4de5-9441-c7be13cd03b3
2 一个 乙 C D 123 2022-10-25 13:22:28.380 657e8971-a6a3-4ab2-9849-ee7060324565
3 一个 乙 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 数据字段1 数据字段2 数据字段3 数据字段4 用户身份 记录时间 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 乙 33 33 456 2022-10-25 13:22:28.393 b1f2e13d-bcc3-4e76-b6a8-05c3a43da619

小提琴

sql-server

1 个回答

  1. Best Answer

    我不完全确定您在执行动态语句时遇到了什么问题，但这很粗略。

    只需使用一堆CASE表达式来动态构建语句。然后在 using 中传递参数sp_executesql。不要将数据直接注入动态部分。

    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;
    • 2

相关问题