SQL Server 2016, Unexpected Result with Stored Procedure Execution-CodePudding

I am by no means a SQL expert. I tend to build and test queries from other sample code that I find and learn in this way. I have a stored procedure that I wrote to update customer pricing in two tables. The gist of what should happen is that if the item and price level exist, it should update that item's price. If it doesn't exist, I need it to insert a new record with that data. When I executed this procedure, it updated every single record in the DB. I would greatly appreciate some insight into best practice in formulating this conditional statement properly. Thanks in advance.

USE [TEST]
GO
/****** Object:  StoredProcedure [dbo].[aUpdatePricingLevels]    Script Date: 5/2/2022 9:59:38 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE   PROCEDURE [dbo].[aUpdatePricingLevels]

@ItemNumber CHAR(31),
@PriceLevel CHAR(11),
@ToQuantity numeric(19, 5) = 999999999999,
@FromQuantity numeric(19, 5) = 1,
@UnitOfMeasure char(9) = 'EA',
@Price numeric(19, 5),
@Equivalency numeric(19, 5) = 1

AS

IF EXISTS(Select * FROM IV00108 WHERE ITEMNMBR like @ItemNumber and PRCLEVEL LIKE @PriceLevel)
    UPDATE IV00108 SET UOMPRICE= @Price  --THERE IS AN ISSUE HERE.  IT SET ALL PRICES. NEEDS A 
CONDITIONAL
    --PRINT'placeholder'

ELSE

INSERT INTO dbo.IV00108 (ITEMNMBR,PRCLEVEL,TOQTY,FROMQTY,UOFM,UOMPRICE,QTYBSUOM)
VALUES(@ItemNumber,@PriceLevel,@ToQuantity,@FromQuantity,@UnitOfMeasure,@Price,@Equivalency)


IF EXISTS(Select * FROM IV00107 WHERE ITEMNMBR like @ItemNumber and PRCLEVEL LIKE @PriceLevel)
    PRINT 'Record Exists, No Action Needed'
    ELSE
INSERT INTO dbo.IV00107 (ITEMNMBR,PRCLEVEL,UOFM,ROUNDHOW,ROUNDTO,UMSLSOPT,QTYBSUOM)
VALUES(@ItemNumber,@PriceLevel,@UnitOfMeasure,0,1,2,@Equivalency)
GO

CodePudding user response：

You're saying:

IF EXISTS (rows where multiple things are true)
  UPDATE the whole table (no filter here).

A much better pattern even if that logic were correct would be:

UPDATE dbo.IV00108
  SET UOMPRICE = @Price
  WHERE ITEMNMBR like @ItemNumber 
    and PRCLEVEL LIKE @PriceLevel;

IF @@ROWCOUNT = 0
BEGIN
  INSERT ...
END

Why don't we want to say:

IF (a row with these conditions exists) 
  UPDATE the row with these conditions

? Because it's twice the work (why locate the row(s) twice?), because it creates a scenario where you can have inconsistencies between them (like in this case), and - most importantly - because it introduces more opportunities for race conditions. See:

Please stop using this UPSERT anti-pattern

Though I will also say using LIKE is odd, especially ITEMNMBR like @ItemNumber. Why not =?

CodePudding user response：

You have a where on your exists but not on your update Here maybe indenting helps...

IF EXISTS(
      Select * 
      FROM IV00108 
      WHERE ITEMNMBR like @ItemNumber and PRCLEVEL LIKE @PriceLevel
)
UPDATE IV00108 SET UOMPRICE= @Price  
WHERE ITEMNMBR like @ItemNumber and PRCLEVEL LIKE @PriceLevel)

UPDATE

As Arron says in another answer -- better to use @@rowcount