An update that deletes rows?Please need expert help!!

[dpwsmw]

here is the situation, I have a front end that calls this SP, any data that I change it brings over ok, But if I dont change it, it deletes the info from the row. So for instance, if I change the CustomerName, it changes the name and updates the table, But then it deletes all the other rows.

Does anyone have an idea how to fix this? Maybe if I write a temp db copying out the row, then copying it back, but that wouldn't keep the change I made. Can anyone Help?

CREATE PROCEDURE p_updateCustomer
(
@CustomerNumber varchar(7),
@CustomerName varchar(30),
@ContactCode varchar(10),
@AddressLine1 varchar(30),
@AddressLine2 varchar(30),
@AddressLine3 varchar(30),
@City varchar(20),
@State varchar(2),
@ZipCode varchar(10),
@CountryCode varchar(3),
@PhoneNumber varchar(17),
@FaxNumber varchar(17),
@Division varchar(2),
@TermsCode varchar(2),
@EmailAddress varchar(50),
@URLAddress varchar(50),
@CreditLimit decimal(19,7),
@AccountId varchar(50)
)
AS
BEGIN
UPDATE AR1_CustomerMaster SET

CustomerNumber = @CustomerNumber,
CustomerName = @CustomerName,
ContactCode = @ContactCode,
AddressLine1 = @AddressLine1,
AddressLine2 = @AddressLine2,
AddressLine3 = @AddressLine3,
City = @City,
State = @State,
ZipCode = @ZipCode,
CountryCode = @CountryCode,
PhoneNumber = @PhoneNumber,
FaxNumber = @FaxNumber,
Division = @Division,
TermsCode = @TermsCode,
EmailAddress = @EmailAddress,
URLAddress = @URLAddress,
CreditLimit = @CreditLimit

WHERE AccountId = @AccountId
END

 

[JayKusch]

The only way I can see doing it would be to have a seperate UPDATE statement for each value. Do a test before each update statement to see if there is a value being passed in for that variable and if so ... Update. If there is no value ... bypass the update statement for that variable.

Thanks

J. Kusch

 

[JamesLean]

You could also do something like this:

CREATE PROC myproc
  @id int,
  @col1 varchar(10) = NULL,
  @col2 varchar(10) = NULL,
  @col3 varchar(10) = NULL
AS

UPDATE mytable
SET col1 = ISNULL(@col1, col1),
  col2 = ISNULL(@col2, col2),
  col3 = ISNULL(@col3, col3)
WHERE id = @id

The only problem then is if you actually want to update the column to NULL. In that case, you would need to use a default value which you would never actually want to update the column to, something like:

CREATE PROC myproc
  @id int,
  @col1 varchar(10) = 'no value',
  ...
AS

UPDATE mytable
SET col1 = CASE WHEN @col1 = 'no value' THEN col1 ELSE @col1 END,
  ...
WHERE id = @id

--James

 

[Jasen]

A 3rd option would be to make it populate all your variables with the data from the row in question first (using the AccountID being passed in), then overwrite those values with any you have changed.
Probably better to just make it do a null check/update for each field individually though.

________
Remember, you're unique... just like everyone else.