Ankur Jain

Ankur Jain

  • NA
  • 816
  • 184.5k

retain old value while update data in sql

Apr 4 2015 2:25 AM
Hi!
any one has idea about this 
retain old value while update data in sql when parameters value is null or empty using procedure?
 
ALTER PROCEDURE [dbo].[sp_UpdateRatesForSubscriberByUtilityIfUserNameNull]
@address_id int ,
@FirstName nvarchar(100) ,
@LastName nvarchar(100) ,
@Address1 nvarchar(512) ,
@City nvarchar(256) ,
@State nvarchar(100) ,
@Zip nvarchar(50) ,
@PublicationID int ,
@RateID int ,
@ExpiryDate datetime ,
@SubscriberID int ,
@Phone nvarchar(250) ,
@Email nvarchar(250) ,
@Result int OUTPUT
AS
BEGIN
SET @Result = 0
Declare @Count int
Select @Count= Count(*) from Subscriber where SubscriberID= @SubscriberID
if(@Count>0)
begin
UPDATE Subscriber
SET address_id = @address_id,
Firstname = @FirstName,
Lastname = @LastName,
Address = @Address1,
City = @City,
State = @State,
Zip = @Zip,
ExpireDate = @ExpiryDate,
Rate_ID = @RateID,
Phone1 = @Phone,
Email = @Email
WHERE SubscriberID = @SubscriberID
AND (@address_id IS NOT NULL or @address_id!=''
OR @Firstname IS NOT NULL OR @Firstname!=''
OR @Lastname IS NOT NULL OR @Lastname!=''
OR @Address1 IS NOT NULL OR @Address1!=''
OR @City IS NOT NULL OR @City!=''
OR @State IS NOT NULL OR @State!=''
OR @Zip IS NOT NULL OR @Zip!=''
OR @ExpiryDate IS NOT NULL OR @ExpiryDate !=''
OR @RateID IS NOT NULL OR @RateID!=''
OR @Phone IS NOT NULL OR @Phone!=''
OR @Email IS Not NULL OR @Email !=''
) ;
UPDATE Payment Set SubscriptionExpiryDate=@ExpiryDate where SubscriberID=@SubscriberID and PublicationID=@PublicationID
UPDATE FreeSubscription Set RequestExpireDate=@ExpiryDate where SubscriberID=@SubscriberID and PublicationID=@PublicationID
AND (@ExpiryDate IS NOT NULL);
IF (@@ERROR <> 0)
BEGIN
SET @Result = -3 -- subscriber details not update in Subscriber table
END
End
Else
Begin
Set @Result=-3
End
RETURN @Result
END
 
 
 
 
 
 
 
 
 
 
 

Answers (4)