Results 1 to 7 of 7

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

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,482

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

    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:
    Code:
    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
    Code:
    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!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,482

    Re: SQL Server - requesting help debugging this stored procedure

    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,482

    Re: SQL Server - requesting help debugging this stored procedure

    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.
    Code:
    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
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: SQL Server - requesting help debugging this stored procedure

    Quote Originally Posted by MMock View Post
    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
    Code:
    ON ((target.relJobControl = source.relJobControl) OR (target.relProjectControl = source.relProjectControl))
     AND (target.EmpID = source.EmpID) AND (target.Team = source.Team)
        WHEN MATCHED THEN
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,230

    Re: SQL Server - requesting help debugging this stored procedure

    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>

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,482

    Re: SQL Server - requesting help debugging this stored procedure

    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
    Code:
     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!
    Code:
    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;
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,482

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

    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width