Only Update Field if Existing Value is NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Only Update Field if Existing Value is NULL

Author

Topic

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-20 : 08:16:19

I was looking for some guidance on the best and most efficient way to achieve the follwing:

I have an application that imports contact data from multiple sources, some contacts may exist in more than one dataset and each dataset may have varying levels of detail for a contact.

I have the SP below that performs an insert only if the contact doesn't already exist otherwise performs an update. The problem is that during an update some existing values could be overwritten with nulls so what i am looking to do is have the query only update a field if the current value is null otherwise ignore it.

Not sure if this is even possible using SQL along but I guessed you gues would be the best people to answer this:


CREATE PROCEDURE dbo.pn_InsertUpdateContact
(
	@FirstName	nvarchar(50) = null,
	@LastName	nvarchar(50)  = null,
	@Address1	nvarchar(50)  = null,
	@Address2	nvarchar(50)  = null,
	@Address3	nvarchar(50)  = null,
	@City		nvarchar(50)  = null,
	@County	nvarchar(50)  = null,
	@Postcode	nvarchar(50)  = null,
	@Country	int = null,
	@Telephone	nvarchar(30)  = null,
	@Email		nvarchar(50)  = null,
	@Gender	char(1)  = null,
	@DateOfBirth	smalldatetime = null,
	@CreatedBy	uniqueidentifier,
	@UpdatedDate	smalldatetime = null,
	@UpdatedBy	uniqueidentifier = null
)

AS

DECLARE @ContactId int

SELECT @ContactId = (SELECT ContactId FROM Contacts WHERE Email = @Email)

IF @ContactID IS NULL
BEGIN
INSERT INTO Contacts (
	FirstName,
	LastName,
	Address1,
	Address2,
	Address3,
	City,
	County,
	Postcode,
	Country,
	Telephone,
	Email,
	Gender,
	DateOfBirth,
	CreatedBy,
	UpdatedDate,
	UpdatedBy)

VALUES (
	@FirstName,
	@LastName,
	@Address1,
	@Address2,
	@Address3,
	@City,
	@County,
	@Postcode,
	@Country,
	@Telephone,
	@Email,
	@Gender,
	@DateOfBirth,
	@CreatedBy,
	@UpdatedDate,
	@UpdatedBy) 
END

ELSE

BEGIN
UPDATE Contacts SET

	FirstName = @FirstName,
	LastName = @LastName,
	Address1 = @Address1,
	Address2 = @Address2,
	Address3 = @Address3,
	City = @City,
	County = @County,
	Postcode = @Postcode,
	Country = @Country,
	Telephone = @Telephone,
	Email = @Email,
	Gender = @Gender,
	DateOfBirth = @DateOfBirth,
	UpdatedDate = getDate(),
	UpdatedBy = @UpdatedBy
	WHERE Email = @Email
END

GO

www.creativenrg.co.uk

pootle_flump

1064 Posts

Posted - 2007-07-20 : 08:59:26

You can use coalesce. Very similar question just posted in new to SQL Server:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86637#320716

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-20 : 09:35:11

quote:
Originally posted by pootle_flump

You can use coalesce. Very similar question just posted in new to SQL Server:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86637#320716

Thanks for that, not come across this keyword before but looks as though it may work so will give it a try.

www.creativenrg.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources