TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
Reply
Answers (
4
)
Auto incremented column in sql
how can transfer data from one database to another database