AdventureWorks 2012 SQL Error when running a special procedure

Question

I am creating a special procedure that applies a inputted discount to an inputted applied quantity but I keep receiving an error where the PK can not be null. Why is it not auto incrementing the row?

GO
CREATE PROCEDURE Sales.uspExcesInvSale
    @DiscountPct smallmoney,
    @ProductInventory int
AS
SET IDENTITY_INSERT Sales.SpecialOffer ON
    INSERT INTO Sales.SpecialOffer (SpecialOfferID, Description, DiscountPct, Type, Category, StartDate, EndDate, MinQty)
    VALUES ((SELECT MAX(SpecialOfferID)+1 FROM SpecialOffer), 'New Sale', @DiscountPct, 'Excess Inventory', 'Direct', DATEADD(DAY,5,GETDATE()), DATEADD(DAY,7,GETDATE()), @ProductInventory);

    INSERT INTO Sales.SpecialOfferProduct (ProductID)
    SELECT ProductID
    FROM Production.ProductInventory 
    GROUP BY ProductID 
    HAVING SUM(Quantity) > (@ProductInventory)
SET IDENTITY_INSERT Sales.SpecialOffer OFF;
GO
BEGIN TRY
EXEC Sales.uspExcesInvSale .5, 1800;
END TRY
BEGIN CATCH
    SELECT  
    ERROR_NUMBER() AS ErrorNumber  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ,ERROR_PROCEDURE() AS ErrorProcedure  
    ,ERROR_LINE() AS ErrorLine  
    ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;
GO

Error received is Error Number 515 Severity 16 Error State 2 Error Procedure Sales.uspExcesInvSale Error Line 9 Error Message Cannot insert the value NULL into column 'SpecialOfferID', table 'AdventureWorks2012.Sales.SpecialOfferProduct'; column does not allow nulls. INSERT fails.

Answer 1

The error is in here - you're not inserting a value for for SpecialOfferID, which is required for this table.

INSERT INTO Sales.SpecialOfferProduct (ProductID)   -- These should have
    SELECT ProductID                                -- SpecialOfferID as well
    FROM Production.ProductInventory 
    GROUP BY ProductID 
    HAVING SUM(Quantity) > (@ProductInventory)

I believe the PKs for the two tables are

• SpecialOffer: single column called SpecialOfferID, int, IDENTITY (e.g., is an auto-increment)
• SpecialOfferProduct: two int columns, of which only one is an IDENTITY. The other is SpecialOfferID

When inserting into SpecialOfferProduct, you therefore need to explicitly specify the SpecialOfferID as it's not automatically entered. The other part of the PK (SalesOrderDetailID I believe) is an IDENTITY and you can just let it do its work.

In contrast - given that the PK of SpecialOffer is an IDENTITY column, I suggest letting it do its work. Remove the IDENTITY_INSERT SET statements, and then don't include a value into the insert statement. It will automatically create the next ID value.

I'm guessing you want to get the relevant SpecialOfferID from the first insert (e.g., from SpecialOffer), and then use that in the second insert (e.g., into SpecialOfferProduct).

You can get the 'last inserted ID' using SCOPE_IDENTITY().

With those changes, here's an example set of code (sadly untested as I don't have the db set up).

NOTE - I am also not 100% sure what you're doing with these inserts (particularly the second) - you will need to check that it's doing what you want. This answer is related to the error re not able to do the insert.

DECLARE @NewID int

INSERT INTO Sales.SpecialOffer (Description, DiscountPct, Type, Category, StartDate, EndDate, MinQty)
    VALUES ('New Sale', @DiscountPct, 'Excess Inventory', 'Direct', DATEADD(DAY,5,GETDATE()), DATEADD(DAY,7,GETDATE()), @ProductInventory);

SET @NewID = SCOPE_IDENTITY()

INSERT INTO Sales.SpecialOfferProduct (SpecialOfferID, ProductID)
    SELECT @NewID, ProductID
    FROM Production.ProductInventory 
    GROUP BY ProductID 
    HAVING SUM(Quantity) > (@ProductInventory)