froodo
froodo

Reputation: 87

sql query not functioning

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

Answers (3)

Luis LL
Luis LL

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

BWS
BWS

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

SoManyGoblins
SoManyGoblins

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

Related Questions