I need support, I have a procedure and I need it to return an error when I try to add the same name in the ProductName column:

USE [Northwind]
        GO
        /****** Object:  StoredProcedure [dbo].[AddNewProduct]    Script Date: 14.09.2021 18:15:53 ******/
        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
        ALTER procedure [dbo].[AddNewProduct]
        
        @ProductName nvarchar(40), @SupplierID int, @CategoryID int, @QuantityPerUnit nvarchar(20), @UnitPrice money, @UnitsInStock smallint, @UnitsOnOrder smallint, @ReorderLevel smallint, @Discontinued bit
        as
        
        If @ProductName = @ProductName
        
        Begin 
            Declare  @count int, @ProductID int 
        
            
        
            Insert into Products
                (
                
                ProductName ,
                SupplierID ,
                CategoryID ,
                QuantityPerUnit,
                UnitPrice,
                UnitsInStock,
                UnitsOnOrder,
                ReorderLevel,
                Discontinued)
                
                
                values
                (@ProductName, 
                @SupplierID , 
                @CategoryID , 
                @QuantityPerUnit, 
                @UnitPrice, 
                @UnitsInStock, 
                @UnitsOnOrder, 
                @ReorderLevel, 
                @Discontinued )
        
        
        End 
        
        else
        Begin
            DECLARE @ErrorMessage NVARCHAR(4000);
            SET @ErrorMessage = 'DDDD';
            RAISERROR (@ErrorMessage, 16, 1);
        end

I want a message to appear when I try to add the same record Can you tell me what I am doing wrong or what needs to be added / changed?

CodePudding user response：

As has been mentioned in the comments, this logic has no right being in the procedure, it should be part of the table's definition, as a UNIQUE CONSTRAINT or UNIQUE INDEX. I'm going to use a UNIQUE CONSTRAINT here, which would mean the DDL for the CONSTRAINT would look like this:

ALTER TABLE dbo.Products ADD CONSTRAINT UQ_ProductName UNIQUE (ProductName);

Then, you just need your procedure to look like this:

ALTER PROCEDURE [dbo].[AddNewProduct] @ProductName nvarchar(40),
                                      @SupplierID int,
                                      @CategoryID int,
                                      @QuantityPerUnit nvarchar(20), --Why is this an nvarchar if it's a quantity?
                                      @UnitPrice money,
                                      @UnitsInStock smallint,
                                      @UnitsOnOrder smallint,
                                      @ReorderLevel smallint,
                                      @Discontinued bit AS
BEGIN
    INSERT INTO dbo.Products(ProductName,
                             SupplierID,
                             CategoryID,
                             QuantityPerUnit,
                             UnitPrice,
                             UnitsInStock,
                             UnitsOnOrder,
                             ReorderLevel,
                             Discontinued)
    VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);        
END;
GO

If you then attempt to insert a duplicate value you'll get the following error:

Violation of UNIQUE KEY constraint 'UQ_ProductName'. Cannot insert duplicate key in object 'dbo.Products'. The duplicate key value is ({Duplicate Product Name}).