Update Query/...

Question

    create procedure USP_Insert_Update_emp(@IntId int ,@chvmobile (20),@Chvename varchar(20),@intOutparameter int)
    as
    begin
       if (@IntId  = 0) --Means user want to insert. /*
         /* Then I check here that, if mobile already
         exists then (In table id is auto generated) and
         for inserting user will enter @IntId  as 0 that
         means he/she want to insert and now  In table id is
         autogenerated and name and mobile is inserted */
         set @intOutparameter = -1 */
       else    /*If user enter @IntId as nonzero i.e. id which he/she want update.*/
        update tblemp set name =@Chvename,
            mobile = @chvmobile
            where id =  @IntId
    end

---

Here now:

1. if user inserts for example "9975072314" and "rishi" values are inserted in the database. Which is acceptebale.

2. If user enters values with same mobile number it gives @intoutputparameter as -1 which is acceptable.

3. now in database tablemps is:

      id    name   mobileNo
      1     nn      123
      2     cvb    1234
      .
      .

      **.
      10    Rishi  9975072314**

4. Now user updates the Id = 2 which makes the values in table as:

      id    name   mobileNo
      1     nn     123
      2     cvb    9975072314  /* Updatable values which I don't want. */
      .
      .
      **.
      10    Rishi  9975072314**

---

Now how is it possible to avoid duplicate Updates in table?

Answer 1

So you don't want duplicates in the mobileNo column?

Option 1:

If mobile no is not an optional field, you can define a UNIQUE constraint on the column:

e.g.

ALTER TABLE tblemp
ADD CONSTRAINT uqtblempMobileNo UNIQUE(mobileNo)

This will result in an error if you try to add a duplicate mobile number into the table. But it would mean, you couldn't have multiple records with a blank/NULL mobile number - I could see it's quite likely that you don't have a mobile no for everyone.

Option 2:

Looks like you do a check in the INSERT code path to make sure that mobile number doesn't already exist. You could just do the check for the UPDATE path too before updating?

IF (ISNULL(@mobileno, '') <> '' 
    AND EXISTS (SELECT * FROM tblemp WHERE mobileno = @mobileno AND id <> @Id))
    BEGIN
        -- A record already exists with this (non-blank) mobile number, and it's NOT the record we're updating. So prevent the UPDATE...

    END