[RESOLVED] SQL Server - requesting help debugging this stored procedure

[MMock]

I hope I just need a fresh pair of eyes to help me look at this, I do not know what's wrong but my sproc is letting me create duplicate rows when, if the row exists, I want to update it. I am using MERGE.

Simple table schema:

CREATE TABLE [dbo].[xtblUsersAndRates](
	[relJobControl] [int] NULL,
	[EmpID] [nvarchar](3) NOT NULL,
	[RateOverridden] [decimal](5, 2) NOT NULL,
	[Team] [nvarchar](64) NOT NULL,
	[relProjectControl] [int] NULL
) ON [PRIMARY]
GO

sproc

ALTER PROCEDURE [dbo].[xsp_UpdateOverriddenRates]
	@relJobControl int = null,
	@relProjectControl int = null,
	@empID nvarchar(3),
	@rateOverridden decimal(5,2),
	@team nvarchar(64)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    MERGE xtblUsersAndRates AS target  
    USING (SELECT @relJobControl, @relProjectControl, @empID, @RateOverridden, @team) AS source (relJobControl, relProjectControl, EmpID, RateOverridden, Team)  
    ON (target.relJobControl = source.relJobControl) AND (target.relProjectControl = source.relProjectControl) AND (target.EmpID = source.EmpID) AND (target.Team = source.Team)
    WHEN MATCHED THEN   
        UPDATE SET RateOverridden = source.RateOverridden 
	WHEN NOT MATCHED THEN  
		INSERT (relJobControl, EmpID, rateOverridden, team, relProjectControl)  
		VALUES (source.relJobControl, source.EmpID, source.RateOverridden, source.Team, source.relProjectControl); 
    --OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;  
END;

How I am calling the sproc: exec xsp_UpdateOverriddenRates null, 2159, '', 250, 'ST'
It keeps creating the same row :-(

Thank you if you can point out what I am not seeing!

[MMock]

I feel like Job or Project being null might have something to do with it. I am going to split this into two sprocs, one I will call when I have a projectControl and one when I have a jobControl to see if those work.

[MMock]

Yes, this one worked better. I can have two, it's fine, but curious how I should handle the nulls. And if I can handle them and it works, of course it's better to have the one sproc.

CREATE PROCEDURE [dbo].[xsp_UpdateOverriddenRatesByProject]
	@relProjectControl int,
	@empID nvarchar(3),
	@rateOverridden decimal(5,2),
	@team nvarchar(64)

AS
BEGIN
	SET NOCOUNT ON;

    MERGE xtblUsersAndRates AS target  
    USING (SELECT @relProjectControl, @empID, @RateOverridden, @team) AS source (relProjectControl, EmpID, RateOverridden, Team)  
    ON (target.relProjectControl = source.relProjectControl) AND (target.EmpID = source.EmpID)
    WHEN MATCHED THEN   
        UPDATE SET RateOverridden = source.RateOverridden 
	WHEN NOT MATCHED THEN  
		INSERT (relProjectControl, EmpID, rateOverridden, team)  
		VALUES (source.relProjectControl, source.EmpID, source.RateOverridden, source.Team); 
    --OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;  
END;
GO

exec xsp_UpdateOverriddenRatesByProject 2159, '', 250, 'ST'
select * from xtblUsersAndRates

[Zvoni]

I feel like Job or Project being null might have something to do with it. I am going to split this into two sprocs, one I will call when I have a projectControl and one when I have a jobControl to see if those work.

Yes, your Problem are the NULL's

Question: In your Data, is it possible, that both "Control"-Fields are NULL at the same time? Or that both are NOT NULL? Or MUST one of them be NOT NULL?
Because if it is either JobControl or ProjectControl is NOT NULL, then you could solve it with an inner OR

Untested though

ON ((target.relJobControl = source.relJobControl) OR (target.relProjectControl = source.relProjectControl))
 AND (target.EmpID = source.EmpID) AND (target.Team = source.Team)
    WHEN MATCHED THEN

[wqweto]

What is the primary key of xtblUsersAndRates? Do you have other candidate keys (i.e. UNIQUEs) on xtblUsersAndRates like on (relJobControl, relProjectControl, EmpID, Team) you are using for look up? If these column are not key (i.e. UNIQUE) there is no reason to use them.

The problem is definately comparing NULLs with equality operator instead of using the dedicated IS NULL one i.e. NULL = x always returns NULL no matter the value of x. Yes, the result of comparisons in SQL langauge can be True, False and NULL i.e. 3-valued logic which is different than 2-valued logic in programming languages and counter intuitive and in predicates (i.e. WHERE clause, ON clause) NULL boolean results are interpreted as not matching.

cheers,
</wqw>

[MMock]

OK, thank you for your help. Some time has passed and those two columns that used to be NULL are now 0 when they don't have a value. I did not even realize that I had changed that until I went to work on this sproc again. So my original "AND" I believe would've worked. However, I am using Zvoni's suggested "OR" since he and also wqweto took the time to help me . Oh, and the reason I changed them from NULL to 0 is what wqweto was asking about, what was the PK. There was no PK at first then during development when access to the table was rather slow I made one. I needed it to be compound on jobControl/projectControl and EmpID. Because yes, you will either have one or the other, never both, and need at least one job or project Control. So my PK is

 CONSTRAINT [PK_UsersAndRates] PRIMARY KEY CLUSTERED 
(
	[relJobControl] ASC,
	[relProjectControl] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Also, the column Team isn't relevant anymore but still in the table, always blank (it may come back in the future). So here's my final sproc and thank you both again for your close attention!

ALTER PROCEDURE [dbo].[xsp_UpdateOverriddenRates]
	@relJobControl int = 0,
	@relProjectControl int = 0,
	@empID nvarchar(3),
	@rateOverridden decimal(5,2)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    MERGE xtblUsersAndRates AS target  
    USING (SELECT @relJobControl, @relProjectControl, @empID, @RateOverridden) AS source (relJobControl, relProjectControl, EmpID, RateOverridden)  
    --ON (target.relJobControl = source.relJobControl) AND (target.relProjectControl = source.relProjectControl) AND (target.EmpID = source.EmpID) 

 ON ((target.relJobControl = source.relJobControl) OR (target.relProjectControl = source.relProjectControl))
 AND (target.EmpID = source.EmpID) AND target.Team = ''
    WHEN MATCHED THEN
        UPDATE SET RateOverridden = source.RateOverridden 
	WHEN NOT MATCHED THEN  
		INSERT (relJobControl, EmpID, rateOverridden, relProjectControl, Team)
		VALUES (source.relJobControl, source.EmpID, source.RateOverridden, source.relProjectControl, ''); 

END;

[MMock]

No, I am back to AND. Sorry I am confused. I thought I tested all paths in the sproc. When I tested from my code I was getting PK violations. Bottom line, if columns allow NULL's you want OR. I am using 0's and AND. Thanks again.