Reputation: 87
ALTER PROCEDURE [dbo].[InsertNewProduct]
@ProductCode VARCHAR(200) ,
@OMCode VARCHAR(200) ,
@ProductName VARCHAR(200) ,
@Category VARCHAR(200) ,
@Balance INT ,
@ReOrder INT ,
@Unit VARCHAR(20) ,
@location VARCHAR(500) ,
@expiry DATE
AS
SET NOCOUNT ON
DECLARE @existingProduct INT
SET @existingProduct = ( SELECT COUNT(*)
FROM dbo.Products
WHERE OMCode = @OMCode
)
IF @existingProduct > 0
BEGIN
RAISERROR(' Already Exists--',11,1)
END
IF @existingProduct <= 0
BEGIN
INSERT INTO dbo.Products
VALUES ( @ProductCode, @OMCode, @ProductName, @Category, @Balance,
@ReOrder, @Unit, @location, @expiry )
END
GO
The issue now is a product with an existing OMCODE still gets inserted. Can't seem to figure why....and i don't want to put a UNIQUE CONSTRAINT on the OMCode Column.
Upvotes: 0
Views: 61
Reputation: 2993
You can try with just one trip to the database, for example:
ALTER PROCEDURE [dbo].[InsertNewProduct]
@ProductCode VARCHAR(200) ,
@OMCode VARCHAR(200) ,
@ProductName VARCHAR(200) ,
@Category VARCHAR(200) ,
@Balance INT ,
@ReOrder INT ,
@Unit VARCHAR(20) ,
@location VARCHAR(500) ,
@expiry DATE
AS
SET NOCOUNT ON
DECLARE @Output TABLE (OMCode VARCHAR(200))
INSERT INTO dbo.Products
OUTPUT INSERTED.OMCode INTO @Output
SELECT @ProductCode, @OMCode, @ProductName, @Category, @Balance, @ReOrder, @Unit, @location, @expiry
FROM (SELECT @OMCode AS OMCode) Tester
LEFT JOIN Products ON Tester.OMCode = Products.OMCode
WHERE Products.OMCode IS NULL
IF(EXISTS(SELECT * FROM [@Output]))
BEGIN
RAISERROR(' Already Exists--',11,1)
END
Personally I like naming explicitly all the columns in the INSERT
clause, it reduces chances of missplacements
...
INSERT INTO dbo.Products (ProductCode, OMCode, ProductName, Category, Balance, ReOrder, Unit, location, expiry)
OUTPUT INSERTED.OMCode INTO @Output
SELECT @ProductCode, @OMCode, @ProductName, @Category, @Balance, @ReOrder, @Unit, @location, @expiry
...
Upvotes: 0
Reputation: 3836
since you're trimming the @OMCode on the input, you may want to try this:
SET @existingProduct = ( SELECT COUNT(*)
FROM dbo.Products
WHERE rtrim(ltrim(OMCode)) = @OMCode
)
Upvotes: 0
Reputation: 5917
Could this be related to concurrency? I see you're not doing any sort of locking in your SP. If the SP is executed twice "simultaneously", both executions could enter the same IF existingProduct <= 0, did you try locking ?
Quick intro here, if it's of any use: http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
Upvotes: 1