SQL Server stored procedure with multiple table insert

Question

I am using SQL Server Management Studio 2014 and I am writing a stored procedure to insert into many tables with foreign keys.

I am getting two errors:

Msg 547, Level 16, State 0, Procedure insertintoorders, Line 163
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Customers__Addre__145C0A3F". The conflict occurred in database "FlowerCompany", table "dbo.Addresses", column 'AddressID'.

Msg 547, Level 16, State 0, Procedure insertintoorders, Line 178
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Orders__Customer__1FCDBCEB". The conflict occurred in database "FlowerCompany", table "dbo.Customers", column 'CustomerID'.

Here is my stored procedure and my testing execution of it:

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'

Although I had read the errors, I am not understanding how to fix them or why they are there.

So your tables already have PK on IDENTITY columns, but you likely don't trust this work to stupid server and do all the work yourself? Like hard ways? — IVNSTN
– IVNSTN, Commented Mar 6, 2016 at 8:48

Jason Goemaat · Accepted Answer · 2016-03-06 08:43:20Z

2

You are using IDENTITY_INSERT and inserting your own values for PhoneId and AddressId. SCOPE_IDENTITY() is always the last value inserted into an identity column, so after you insert into phone it will return PhoneId and never AddId.

Option 1: Use the values you have. This is a bad idea because if you do two adds at the same time, you might get conflicts

SET IDENTITY_INSERT dbo.Customers ON
INSERT INTO Customers (CustomerID,AddressID,PhoneID,FirstName,LastName)
VALUES (@CustID, @AddId, @PhoneId ,@FirstName, @LastName)
SET IDENTITY_INSERT dbo.Customers OFF

Option 2: Just let the system set the identity values and get them after each insert, i.e.:

INSERT INTO Addresses (Street, City, States, Zip)
VALUES (@Street,@City,@State,@Zip)
SET @AddId = SCOPE_IDENTITY()

edited Mar 6, 2016 at 8:43

answered Mar 6, 2016 at 8:36

Jason Goemaat

29.3k15 gold badges89 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wiredlime2015 Over a year ago

I changed things to option 2 and I am getting the same error

IVNSTN Over a year ago

remove all your select max+1 part totally

IVNSTN Over a year ago

Fill in each variable with SCOPE_IDENTITY value right after appropriate insert statement. Just like @Jason demonstrated with example for Addresses.

Collectives™ on Stack Overflow

SQL Server stored procedure with multiple table insert

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related