CODEWITHSUNDEEP
sql-server
Benson O.
Benson O.

Reputation: 117

Is there any reason WHERE NOT EXISTS does not seem to work in my statement?

This is my DDL:

CREATE TABLE dbo.KeyName
(
KeyNameID INT NOT NULL  IDENTITY(1,1),
PartName VARCHAR(20) NOT NULL ,
RwVersion ROWVERSION NOT NULL 
CONSTRAINT PK_KeyName PRIMARY KEY CLUSTERED (KeyNameID)
);

This is my stored procedure:

ALTER PROCEDURE [dbo].[uspKeyNameUpdate]
@KeyNameID INT,
@PartName VARCHAR(20),
@RwVersion BIGINT

AS

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

BEGIN
   DECLARE @newId INT = 0
   
      INSERT INTO dbo.KeyName(PartName) VALUES(@PartName)
      SELECT @PartName AS PartName
      WHERE NOT EXISTS (
      SELECT PartName FROM dbo.KeyName WHERE PartName = @PartName)

      SET @newId = SCOPE_IDENTITY()

      IF ISNULL(@newId,0) != 0
         INSERT INTO dbo.EmployeeName(KeyNameID,EmployeeID) VALUES(@newId,@KeyNameID)
END

The sp works if PartName does not exist in the KeyName table. However when I call it the second time with the same details it fails with the following error:

Cannot insert duplicate key row in object 'dbo.KeyName' with unique index 'UX_KeyName'. The duplicate key value is (winston).
The statement has been terminated.

I replaced the INSERT block with the following:

   IF NOT EXISTS(SELECT * FROM KeyName WHERE PartName = @PartName)
      INSERT INTO KeyName(PartName) VALUES(@PartName)
      SELECT @newId = SCOPE_IDENTITY()
      INSERT INTO EmployeeName(KeyNameID,EmployeeID) VALUES(@newId,@KeyNameID)

The same error:

I am at sea currently, please help.

Upvotes: 0

Views: 277

Answers (2)

Thom A
Thom A

Reputation: 96028

I think the problem is that you think that the statements below are one statement:

      INSERT INTO dbo.KeyName(PartName) VALUES(@PartName)
      SELECT @PartName AS PartName
      WHERE NOT EXISTS (
      SELECT PartName FROM dbo.KeyName WHERE PartName = @PartName)

These are not 1 statement, they are two; an INSERT statement and a SELECT statement. If we terminate your statements, which you should be doing anyway as not doing so is deprecated, and add some additional formatting perhaps this becomes more clear:

      INSERT INTO dbo.KeyName(PartName)
      VALUES(@PartName);

      SELECT @PartName AS PartName
      WHERE NOT EXISTS (SELECT PartName
                        FROM dbo.KeyName
                        WHERE PartName = @PartName);

As you can see, as a result the parameter, @PartName will always be INSERTed into your table regardless of if it exists or not.

Likely you actually want something like this:

ALTER PROCEDURE [dbo].[uspKeyNameUpdate] @KeyNameID int,
                                         @PartName varchar(20),
                                         @RwVersion bigint
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.KeyName WHERE PartName = @PartName)
    BEGIN
        INSERT INTO dbo.KeyName (PartName)
        SELECT @PartName;

        INSERT INTO dbo.EmployeeName (KeyNameID,
                                      EmployeeID)
        VALUES (SCOPE_IDENTITY(), @KeyNameID);
    END;
END;

Upvotes: 6

Developer09
Developer09

Reputation: 189

I think you are having trouble with the INSERT-SELECT syntax.

Try removing "VALUES(@PartName)" and it should be fine.

Upvotes: 2

Related Questions