Finding a string it split strings

I'm having a column "SelectedCustomers" in the database table holding selected customers that mana开发者_如何学Goger is editing there details NOW. For example it can contain this string '111,222,333' which means that at this current time the manager is selected to edit customers with these ids 111,222 and 333. If another manager is trying to edit a customer that already editing by the first manager the second manager should get an error, preventing him from editing this customer. This check should hold at the sql. And lets say that checking store procedure getting a nvarchard "CheckCustomers" with this value : '234,222,341'. And because the second manager is trying to edit customer with id 222 that is already selected for editing by the first manager. The second manager will get an error message. How should the sql query should be? (I have already a "split" function.)

SELECT * FROM dbo.test WHERE dbo.Split(SelectedCustomers)IN (CheckCustomers)

Why not simply add another column 'IsBeingEdited' in the customer's table? You can then simply:

SELECT id FROM customer WHERE id IN (CheckCustomers) AND IsBeingEdited = 1;

The list shows id's of customer currently being edited, right (and easily changable to other column like name)? So you can also shows that to the 2nd manager.

You can do this with CROSS APPLY.

IF EXISTS (
    SELECT S.CustomerID
    FROM dbo.test T
    CROSS APPLY dbo.Split(T.SelectedCustomers) S
    CROSS APPLY dbo.Split(T.CheckCustomers) C
    WHERE S.CustomerID = C.CustomerID
)
BEGIN
    RAISERROR('Customer is locked by another user', 16, 0)
END

I suggest that this design be changed. Any time do something like splitting strings to get at the 'hidden' fields inside the string is necessary it's a violation of one of the basic principles of using a relational database, which is that each field in a row should store a single value. Can this be made to work? Probably. Is it a good idea to do so? Not in my opinion.

As others have pointed out there are several ways to change the design. A column could be added to the Customer table to indicate that the customer is locked. A separate table (CustomerLocked, for example) containing the CustomerID and the manager's ID could be used - this would allow additional information to be added such as the time that the customer was locked, etc, which might be useful if someone were to lock a customer and then walk away from their desk.

I believe that either of these changes would satisfy the requirements. For example, let's say that a LockedCustomer table is created:

Table LockedCustomer
  CustomerID    NUMBER  PRIMARY KEY
  ManagerID     VARCHAR
  AddDate       DATE

and let's say that manager A has locked customers 111, 222, and 333; thus in the LockedCustomer table the following rows would exist:

CustomerID      ManagerID
111             A
222             A
333             A

Now along comes manager B, who wishes to lock customer 222. The application manager B is using attempts to insert a new row into the LockedCustomer table, as follows:

INSERT INTO LockedCustomer (CustomerID, ManagerID)
  VALUES (222, 'B');

This statement should fail because CustomerID 222 already exists in the table, and the CustomerID column is the primary key on LockedCustomer. This makes sense as we only want a given CustomerID to exist at most one time in the LockedCustomer table at any point in time. The application manager B is using could then detect that the INSERT failed due to a primary key constraint violation, and would understand that this meant that the customer could not be locked at this time. By re-querying the LockedCustomer table for additional data, as in:

SELECT *
  FROM LockedCustomer
  WHERE CustomerID = 222

the application could present a dialog to Manager B that might look something like

The customer you wished to edit (222) is currently in use
by Manager A since 03-Jun-2011 2:17 PM.  Would you like to
  A) Wait
  B) Send an email to Manager A
  C) Take a long vacation
  D) Violate company policy regarding alcohol consumption
     during working hours
Please select one of the above options?

Share and enjoy.