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
Vikrant MorePosted Apr 12, 2015, 4:27 AM
Gowtham RajamanickamPosted Apr 10, 2015, 1:10 PM
saravanagopi sPosted Apr 9, 2015, 6:47 AM
select Coalesce(@FirstName,select FirstName from subscriber where Subscriberid=@subscriberid)
this is avoid null value
or use case statement
update subscriber set FirstName = case when @FirstName is null or @FirstName = '' then (select FirstName from subscriber where subscriberid = @subscriberid)
else @FirstName
end,
LastName= case when @LastNameis null or @input = '' then (select LastName from subscriber where subscriberid = @subscriberid)
else @LastName
end
where where subscriberid = @subscriberid
Pankaj Kumar ChoudharyPosted Apr 5, 2015, 7:47 PM