SQL Server Upt Stored Proc Duplicating Records

[heatherb0123]

I'm trying to create a stored procedure that updates records when they exist, and creates a new record when they don't. The code I'm using is duplicating the existing records. Can anyone help?
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

USE [cashNotification]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Upt_DCF_Balances]
@HiNetMajor varchar (12),
@HiNetMinor varchar (12),
@PostDate datetime,
@Currency varchar (3),
@Balance float,
@BalanceType varchar (50),
@Source varchar (50),
@UserID varchar (12)
AS
BEGIN
SET NOCOUNT ON;

F NOT EXISTS (SELECT HiNetMinor FROM tblDCF_Balances WHERE HiNetMajor = @HiNetMajor AND HiNetMinor = @HiNetMinor AND EffDate = @PostDate AND CurrCode = @Currency AND BalanceType = @BalanceType)
BEGIN
INSERT INTO tblDCF_Balances (
HiNetMajor,
HiNetMinor,
EffDate,
CurrCode,
Balance,
BalanceType,
DataSource,
UserID)
SELECT
@HiNetMajor,
@HiNetMinor,
@PostDate,
@Currency,
@Balance,
@BalanceType,
@Source,
@UserID
END
ELSE
BEGIN
UPDATE tblDCF_Balances
SET
Balance = @Balance,
DataSource = @Source,
UserID = @UserID
WHERE HiNetMajor = @HiNetMajor AND
HiNetMinor = @HiNetMinor AND
EffDate = @PostDate AND
CurrCode = @Currency AND
BalanceType = @BalanceType
END

--Grant execute on Upt_DCF_Balances to public
END

Heather B...

 

[SimonSellick]

Are you passing NULL values in one or more parameters? They don't match anything so the NOT EXISTS will always return true. Demo:

set nocount on
create table a(a int)
declare @a int
set @a = null
insert a(a) values(null)
if not exists (select 1 from a where a = @a) print 'no match' else print 'match'
drop table a

HTH
Simon

 

[heatherb0123]

Yes I am passing in a null value for "HiNetMinor". I changed the code (see below) and it still doesn't work. Any suggestions?
------------------------------------------------------------

USE [cashNotification]
GO
/****** Object: StoredProcedure [dbo].[Upt_DCF_Balances] Script Date: 12/17/2010 07:58:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Upt_DCF_Balances]
@HiNetMajor varchar (12),
@HiNetMinor varchar (12),
@PostDate datetime,
@Currency varchar (3),
@Balance float,
@BalanceType varchar (50),
@Source varchar (50),
@UserID varchar (12)
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS (SELECT HiNetMajor FROM tblDCF_Balances WHERE HiNetMajor = @HiNetMajor AND HiNetMinor = @HiNetMinor AND EffDate = @PostDate AND CurrCode = @Currency AND BalanceType = @BalanceType)

BEGIN
UPDATE tblDCF_Balances
SET
Balance = @Balance,
DataSource = @Source,
UserID = @UserID
WHERE
HiNetMajor = @HiNetMajor AND
HiNetMinor = @HiNetMinor AND
EffDate = @PostDate AND
CurrCode = @Currency AND
BalanceType = @BalanceType
END
ELSE
BEGIN
INSERT INTO tblDCF_Balances (
HiNetMajor,
HiNetMinor,
EffDate,
CurrCode,
Balance,
BalanceType,
DataSource,
UserID)
SELECT
@HiNetMajor,
@HiNetMinor,
@PostDate,
@Currency,
@Balance,
@BalanceType,
@Source,
@UserID
END

--Grant execute on Upt_DCF_Balances to public
END

Heather B...

 

[SimonSellick]

That won't work, unfortunately - expressions which contain NULL evaluate to NULL which T-SQL interprets as false - so inverting the condition and the then...else code does not help.

One way to avoid it is to protect the comparisons with IsNull, using an otherwise unused value (-1 in the example):

IF EXISTS (
  SELECT 1 
  FROM table 
  WHERE IsNull(col, -1) = IsNull(@param, -1)
)
BEGIN
  update...
END 
ELSE
BEGIN
  insert...
END

Another is always to try the insert and catch the complaint (exception) when a duplicate would be created, but that works only if you have suitable constraints on the table.

HTH
Simon

 

[heatherb0123]

I can't change the table structure, so I'm forced to do this in the stored proc. What if I don't know which parameter would be null at any given time? How do I code this? I'm a newbie so I really appreciate your help!

Heather B...

 

[gmmastros]

Replace your current IF EXISTS check with this one:

IF EXISTS (SELECT HiNetMajor FROM tblDCF_Balances WHERE Coalesce(HiNetMajor, '') = Coalesce(@HiNetMajor, '') AND Coalesce(HiNetMinor, '') = Coalesce(@HiNetMinor, '') AND Coalesce(EffDate, 0) = Coalesce(@PostDate, 0) AND Coalesce(CurrCode, '') = Coalesce(@Currency, '') AND Coalesce(BalanceType, '') = Coalesce(@BalanceType, ''))

Notice that nulls for strings are converted to empty strings for comparison purposes. Nulls int he EffDate/@PostDate are converted to the 0 date which is Jan 1,1900. These values are only converted for comparison purposes, the "converted" value is not stored anywhere.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[heatherb0123]

Thanks everyone! George I changed my "IF EXISTS" statement (see the code below). It's no longer duplicating records, however the update function is not working now.

USE [cashNotification]
GO
/****** Object: StoredProcedure [dbo].[Upt_DCF_Balances] Script Date: 12/17/2010 13:33:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Upt_DCF_Balances]
@HiNetMajor varchar (12),
@HiNetMinor varchar (12),
@PostDate datetime,
@Currency varchar (3),
@Balance float,
@BalanceType varchar (50),
@Source varchar (50),
@UserID varchar (12)
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS (SELECT HiNetMajor
FROM tblDCF_Balances
WHERE
Coalesce(HiNetMajor, '') = Coalesce(@HiNetMajor, '') AND
Coalesce(HiNetMinor, '') = Coalesce(@HiNetMinor, '') AND
Coalesce(EffDate, 0) = Coalesce(@PostDate, 0)AND
Coalesce(CurrCode, '') = Coalesce(@Currency, '') AND
Coalesce(BalanceType, '') = Coalesce(@BalanceType, ''))

BEGIN
UPDATE tblDCF_Balances
SET
Balance = @Balance,
DataSource = @Source,
UserID = @UserID
WHERE
HiNetMajor = @HiNetMajor AND
HiNetMinor = @HiNetMinor AND
EffDate = @PostDate AND
CurrCode = @Currency AND
BalanceType = @BalanceType
END
ELSE
BEGIN
INSERT INTO tblDCF_Balances (
HiNetMajor,
HiNetMinor,
EffDate,
CurrCode,
Balance,
BalanceType,
DataSource,
UserID)
SELECT
@HiNetMajor,
@HiNetMinor,
@PostDate,
@Currency,
@Balance,
@BalanceType,
@Source,
@UserID
END

--Grant execute on Upt_DCF_Balances to public
END

Heather B...

 

[gmmastros]

I think the problem here is that you don't understand how NULLS compare, so let me explain.

It often times helps to think of NULL as unknown. When you are comparing things, UNKNOWN does not compare at all. How can it? In fact, the result would be unknown.

For example, think about your family. Imagine you had lots of information about your family in a table. Now suppose you wanted to get a list of family members with the same last name as you. You probably know the last names of all the members in your family so there is not any unknown's to compare to. But, now suppose you wanted to get a list of family members that got an average grade of B in 9th grade English class. You might have this information in your table for some of your family members, but not all of them. So the grade would be null. Now, if you write a query like this:

Select Name From Family Where Grade9English = 'B'

You will get a certain list of names, but if you don't know if Cousin Leonard get a B (and you store NULL in that column for him), he cannot be included in the results because you don't know if he got a B or not. Now suppose you wrote a similar query:

Select Name From Family Where Grade9English <> 'B'

Again... you don't know what grade Cousin Leonard got, so you don't know if he DIDN'T get a B. Again, Cousin Leonard would not be included in the results.

Lastly, you CAN get your family members where you don't know what grade they got with this query.

Select Name From Family Where Grade9English Is NULL

If you run all 3 queries I just presents, each and every row would appear in one of the queries. Bottom line, to compare NULL, you need to use IS NULL.

Now let's look at your update:

        UPDATE tblDCF_Balances
        SET 
            Balance = @Balance, 
            DataSource = @Source,
            UserID = @UserID
        WHERE
            HiNetMajor = @HiNetMajor AND
            HiNetMinor = @HiNetMinor AND
            EffDate = @PostDate AND 
            CurrCode = @Currency AND
            BalanceType = @BalanceType

I suspect that you have NULL's in your table and I also suspect you are passing NULL's in your parameters. Since NULLs don't compare true to anything (even other NULL's), you should think about converting NULLs to empty strings like we did for the Exists part.

        UPDATE tblDCF_Balances
        SET 
            Balance = @Balance, 
            DataSource = @Source,
            UserID = @UserID
        WHERE
            Coalesce(HiNetMajor, '')  = Coalesce(@HiNetMajor, '') AND
            Coalesce(HiNetMinor, '')  = Coalesce(@HiNetMinor, '') AND
            Coalesce(EffDate,  0)     = Coalesce(@PostDate, 0) AND 
            Coalesce(CurrCode, '')    = Coalesce(@Currency, '') AND
            Coalesce(BalanceType, '') = Coalesce(@BalanceType, '')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[heatherb0123]

Thanks sooooo much George!!!!!! That worked perfect and I have a better understanding of working with nulls now!

Heather B...

 

[markros]

If you're using SQL Server 2008, then use MERGE operator instead of IF NOT EXISTS / UPDATE logic.

PluralSight Learning Library