Stored Procedure for Multi-Table Insert Error: Cannot Insert the Value Null into Column
- by SidC
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
