Stored Procedure for Multi-Table Insert Error: Cannot Insert the Value Null into Column

Question

Good Evening All,

I've created the following stored procedure:

CREATE PROCEDURE AddQuote
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @CompanyName nvarchar(50),
    @Addr nvarchar(50),
    @City nvarchar(50),
    @State nvarchar(2),
    @Zip nvarchar(5),
    @NeedDate datetime,
    @PartNumber float,
    @Qty int
-- Insert statements for procedure here
Insert into dbo.Customers
(CompanyName, Address, City, State, ZipCode)
Values (@CompanyName, @Addr, @City, @State, @Zip)

Insert into dbo.Orders 
(NeedbyDate)
Values(@NeedDate) 

Insert into dbo.OrderDetail
(fkPartNumber,Qty)
Values (@PartNumber,@Qty)


END
GO

When I execute AddQuote, I receive an error stating:

Msg 515, Level 16, State 2, Procedure AddQuote, Line 31
Cannot insert the value NULL into column 'ID', table 'Diel_inventory.dbo.OrderDetail'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I understand that I've set Qty field to not allow nulls and want to continue doing so. However, are there other syntax changes I should make to ensure that this sproc works correctly?

Thanks, Sid

Answer 1

Looks to me like you forgot to enable "identity specification" for the ID column of the OrderDetail table. It has nothing to do with your stored procedure per se.

You will need to recreate the table to make the ID column have IDENTITY, but SQL Management Studio will script that for you.

Answer 2

• You have an ID column in the OrderDetail table
• You are not giving a value for ID in the INSERT

so

• change the OrderDetail insert to give a value
• or ensure the column has an IDENTITY
• or ensure it has a default (not useful if it's the PK but generally it's an option)