如何重构重复的SQL更新查询？

I have 3 tables.

我有3张桌子。

1. Users 4 Cols
UserID - User - RealName - Flags

2. UsersGroups 2 Cols
UserID - GroupID

3. Groups 3 Cols
GroupID - Group - Flags

and I want to set the flags on the User table for User = 'Administrator' and apply the same to the Group table.

我想在User表的User ='Administrator'上设置标志,并将其应用于Group表。

I have the following SQL that works, but I also have several flags that I have to apply using bitwise operators.

我有以下SQL工作,但我也有几个标志,我必须使用按位运算符。

I've found my code to be really repetitive, so was wondering if anyone could suggest some refactoring that would not affect the performance.

我发现我的代码非常重复,所以想知道是否有人可以建议一些不会影响性能的重构。

Code:

--- SET FLAG 1      
UPDATE User
SET User.Flags = User.Flags | 2048
WHERE User.Value = 'Administrator'

UPDATE dbo.Group
SET dbo.Group.Flags = 
    (Select e.Flags FROM dbo.User p INNER JOIN dbo.UserInGroup pe ON p.UserID = pe.UserID
    INNER JOIN dbo.Group e ON e.GroupID = pe.GroupID
    WHERE p.Value = 'Administrator') | 2048

FROM dbo.User p INNER JOIN dbo.UserInGroup pe ON p.UserID = pe.UserID
INNER JOIN dbo.Group e ON e.GroupID = pe.GroupID
WHERE p.Value = 'Administrator'

-- SET FLAG 2       
UPDATE User
SET User.Flags = User.Flags | 512
WHERE User.Value = 'Administrator'

UPDATE dbo.Group
SET dbo.Group.Flags = 
    (Select e.Flags FROM dbo.User p INNER JOIN dbo.UserInGroup pe ON p.UserID = pe.UserID
    INNER JOIN dbo.Group e ON e.GroupID = pe.GroupID
    WHERE p.Value = 'Administrator') | 512

FROM dbo.User p INNER JOIN dbo.UserInGroup pe ON p.UserID = pe.UserID
INNER JOIN dbo.Group e ON e.GroupID = pe.GroupID
WHERE p.Value = 'Administrator'

2 个解决方案

#1

You could create a stored procedure which takes the flag bit value and the user/group name as arguments and use them as parameters for your queries - then call the stored procedure when you need to change a flag

您可以创建一个存储过程,它将标志位值和用户/组名称作为参数,并将它们用作查询的参数 - 然后在需要更改标志时调用存储过程

Something like (untested)

像(未经测试的)

create proc usp_set_flags
    @flag int
    ,@username varchar(50)
AS
UPDATE User
SET User.Flags = User.Flags | @flag 
WHERE User.Value = @username 

UPDATE dbo.Group
SET dbo.Group.Flags = 
    (Select e.Flags FROM dbo.User p INNER JOIN dbo.UserInGroup pe ON p.UserID = pe.UserID
    INNER JOIN dbo.Group e ON e.GroupID = pe.GroupID
    WHERE p.Value =  @username) | @flag

FROM dbo.User p INNER JOIN dbo.UserInGroup pe ON p.UserID = pe.UserID
INNER JOIN dbo.Group e ON e.GroupID = pe.GroupID
WHERE p.Value =  @username
GO

(This is a vary basic example - it would be a good idea to add some validation and error checking.)

(这是一个不同的基本示例 - 添加一些验证和错误检查是个好主意。)

You'd then call it like so:

然后你会这样称呼它:

exec usp_set_flags @flag = 2048, @username = 'Administrator'

#2

If your goal is to remove UPDATE clauses for groups table, then simply create an UPDATE trigger on users table that updates groups table.

如果您的目标是删除groups表的UPDATE子句,那么只需在users表上创建更新groups表的UPDATE触发器。

#1