Click here to Skip to main content
15,124,187 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want a stored procedure that takes the following as an input
Name Mobile and email

I need to update the table with these new values.
Table fields are
Id Name Mobile email VerifyPhone VerifyEmail


However when updating i need to check if the new inserted phone number is the same as the old value.. If not then i need to set verifyPhone to false

Also i need to check the email value if it changed or not.. If it changed i will need to update verifyEmail to false

What I have tried:

How can i do it in a good way?
Normaly i would say
Declare @oldPhone nvarchar(10) 
Declare @oldEmail nvarchar(100)
Declare @verifyEmail bit =1
Declare @verifyPhone bit =1

Select @oldPhone =phone, @oldEmail =email from table1where Id=@Id
If(@oldEmail = @email)
Begin
@VerifyEmail =0
End

If(@oldPhone = @phone)
Begin
@VerifyPhone =0
End

Update table1 set name=@name,phone=@phone, email =@email,verifyEmail =@verifyemail,verifyphone=@verifyPhone where table1.Id =@Id


Can i do this in one select statement or in a better way?
Posted
Updated 14-Jul-21 23:20pm

1 solution

Try:
SQL
UPDATE
    table1
SET
    Name = @Name,
    Phone = @Phone,
    Email = @Email,
    VerifyPhone = CASE WHEN Phone = @Phone THEN VerifyPhone ELSE 0 END,
    VerifyEmail = CASE WHEN Email = @Email THEN VerifyEmail ELSE 0 END
WHERE
    Id = @Id
;
NB: You don't want to set the Verify* columns to 1 if the values are the same, in case the existing values haven't been verified yet. Otherwise a user can skip your verification process entirely by simply updating their details without changing them.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900