title: SQL Server stored procedure with multiple table insert
body: <p>I am using SQL Server Management Studio 2014 and I am writing a stored procedure to insert into many tables with foreign keys.</p>
  
  <p>I am getting two errors:</p>
  
  <blockquote>
    <p>Msg 547, Level 16, State 0, Procedure insertintoorders, Line 163<br>
    The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Customers__Addre__145C0A3F". The conflict occurred in database "FlowerCompany", table "dbo.Addresses", column 'AddressID'.</p>
    
    <p>Msg 547, Level 16, State 0, Procedure insertintoorders, Line 178<br>
    The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Orders__Customer__1FCDBCEB". The conflict occurred in database "FlowerCompany", table "dbo.Customers", column 'CustomerID'.</p>
  </blockquote>
  
  <p>Here is my stored procedure and my testing execution of it:</p>
  
  <pre><code>CREATE PROCEDURE insertintoorders
      @Street varchar(50), 
      @City varchar(30), 
      @State varchar(2), 
      @Zip varchar(9),
      @Phone varchar(10),
      @FlowerName varchar(50), 
      @FirstName varchar(40),
      @LastName varchar(40),
      @OrderStatus varchar(12),
      @DeliverDate date,
      @OrderMessage varchar(100), 
      @OrderDate date, 
      @Vase bit, 
      @OrderCost decimal(6,2)
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      DECLARE @flower int;
  
      SELECT @flower = Arr.FlowerID
      FROM Arrangements Arr
      WHERE FlowerName = @FlowerName
  
      DECLARE @AddID int;
      SELECT @AddID = coalesce((SELECT MAX(AddressID) + 1 FROM Addresses), 1)
  
      DECLARE @PhoneID int;
      SELECT @PhoneID = coalesce((SELECT MAX(PhoneID) + 1 FROM Phone), 1)
  
      DECLARE @CustID int;
      SELECT @CustID = coalesce((SELECT MAX(CustomerID) + 1 FROM Customers), 1)
  
      DECLARE @Del int;
      SELECT @Del = coalesce((SELECT MAX(DeliveryID) + 1 FROM Delivery), 1)
  
      DECLARE @Ords int;
      SELECT @Ords = coalesce((select max(StatusID) + 1 from OrderStatus), 1)
  
      DECLARE @Ord int;
      SELECT @Ord = coalesce((select max(OrderID) + 1 from Orders), 1)
  
      INSERT INTO Addresses (Street, City, States, Zip)
      VALUES (@Street, @City, @State, @Zip)
  
      SET @AddId = SCOPE_IDENTITY()
  
      INSERT INTO Phone (Phone)
      VALUES (@Phone)
  
      SET @PhoneID = SCOPE_IDENTITY()
  
      INSERT INTO Customers (AddressID, PhoneID, FirstName, LastName)
      VALUES (SCOPE_IDENTITY(), SCOPE_IDENTITY(), @FirstName, @LastName)
  
      SET @CustID = SCOPE_IDENTITY()
  
      INSERT INTO Delivery (DeliverDate)
      VALUES (@DeliverDate)
  
      SET @Del = SCOPE_IDENTITY()
  
      INSERT INTO OrderStatus (OrderStatus)
      VALUES (@OrderStatus)
  
      SET @Ords = SCOPE_IDENTITY()
  
      INSERT INTO Orders ([CustomerID], [FlowerID], [StatusID],[DeliveryID], OrderMessage, OrderDate, OrderCost, Vase)
      VALUES (SCOPE_IDENTITY(), @flower, SCOPE_IDENTITY(), SCOPE_IDENTITY(), @OrderMessage, @OrderDate, @OrderCost, @Vase)
  
      SET @Ord = SCOPE_IDENTITY()
  END
  GO
  
  EXEC insertintoorders @Street = '555 LANE', @City='Somewhere', @State = 'XX', @Zip = '99999', @Phone = '1234567896', @FlowerName = 'The Flower of Love', @FirstName = 'George', 
  @LastName = 'Fish', @DeliverDate = '10/10/2016', @OrderStatus = 'Completed', @OrderMessage = 'Fishy flowers', @OrderDate = '03/03/2016', @OrderCost = '200', @Vase = '1'
  </code></pre>
  
  <p>Although I had read the errors, I am not understanding how to fix them or why they are there.</p>