How do I refactor repetitive SQL update queries?

Question

I have 3 tables.

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.

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

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'

Answer 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'

Answer 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.