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?

I had a re-read, and think I get it now. Clarified my answer. Just wants to prevent multiple records having the same mobile number. Already catering for it in the INSERT code path, but needs help with the UPDATE codepath. I think. — AdaTheDev
– AdaTheDev, Commented Dec 19, 2009 at 11:56

AdaTheDev · Accepted Answer · 2009-12-19 11:55:21Z

2

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

edited Dec 19, 2009 at 11:55

answered Dec 19, 2009 at 11:26

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hrishi Over a year ago

thanks man ...I have one more question...when i am executing Sp from sql server management studio by right clicking on sp_name it returns value...What is this return valur means...

AdaTheDev Over a year ago

A RETURN value can be used to indicate the result of executing the sproc e.g. in your sproc you could have a return code to indicate a success, or a failure by using "RETURN <IntegerCodeGoesHere>". Default is 0.

Collectives™ on Stack Overflow

Update Query/

1 Answer 1

Option 1:

Option 2:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option 1:

Option 2:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related