The following table, Approvers, has this data:
DECLARE @Approvers TABLE (ID INT, EmployeeID INT, OrderID INT)
INSERT INTO @Approvers (ID, EmployeeID, OrderID) VALUES (1, 28463, 356024)
INSERT INTO @Approvers (ID, EmployeeID, OrderID) VALUES (5, 364486, 356024)
INSERT INTO @Approvers (ID, EmployeeID, OrderID) VALUES (6, 368945, 356024)
INSERT INTO @Approvers (ID, EmployeeID, OrderID) VALUES (7, 368945, 356051)
--ID EmployeeID OrderID
--1 28463 356024
--5 364486 356024
--6 368945 356024
--7 368945 356051
When an Employee is disabled, any entry in this table should be replaced by another on by choosing from a list. But if the new choice already exists for that OrderID, do not update (it will be disabled by another script).
DECLARE @UserID INT = 368945, --inactive
@ApproverID INT = 364486 -- new approver
--expecting ID = 7
SELECT ID FROM @Approvers
WHERE EmployeeID = @UserID
AND NOT EXISTS (????????)
As you can see, I'm a little stuck on this and have tried several attempts but no luck.
Any input is appreciated.