Adding multiple parent records and multiple child records in single stored procedure

Question

I feel I need to start this question by saying I have a very limited knowledge of SQL, therefore the solution I am trying to implement has come about from a lot of reading and lack of experience. So if what I am requesting is the wrong way to complete my task please advise accordingly (rather than accepting that I must have some valid reason for creating a potentially really odd SQL statement).

The problem

I have two tables Rooms and RoomPhotoUrls I am receiving the data for these in a table value parameter (TVP). The TVP can have n rooms and each room can have n images. I would like to be able insert all rooms then get the id for each room inserted so that I can insert the images and associate them to the correct room

Why I've chosen to try this as a potential solution

As you can see from the rooms table it's a child of another table(properties). The properties tables also has other tables to populate so I want to make a single call to the database passing my property object and have SQL manage all the data inserts\updates rather than making several round trips.

Having limited knowledge of SQL I did a lot of reading and found TVP's which seem to be what I need to use. I also read about passing xml parameters, but I got the impression that passing xml was only a solution as TVP hadn't been introduced, so I went with the TVP option.

The code I have added

Below you will see the following:

• RoomPhotoUrls Table schema
• Rooms Table schema
• TVP schema
• Some sample data
• My stored procedure
• The result of my stored procedure

Table schemas

CREATE TABLE [dbo].[RoomPhotoUrls]
(
    [RoomPhotoUrlsId] [int] IDENTITY(1,1) NOT NULL,
    [RoomId] [int] NULL,
    [Url] [varchar](max) NULL,

    CONSTRAINT [PK_RoomPhotoUrls] 
        PRIMARY KEY CLUSTERED ([RoomPhotoUrlsId] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[Rooms]
(
    [RoomId] [int] NULL,
    [PropertyId] [int] NULL,
    [RoomName] [varchar](120) NULL,
    [RoomDescription] [varchar](max) NULL,
    [RoomLength] [decimal](5, 2) NULL,
    [RoomWidth] [decimal](5, 2) NULL,
    [Url] [varchar](max) NULL

    CONSTRAINT [PK_Rooms] 
        PRIMARY KEY CLUSTERED ([RoomId] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Table-valued parameter

CREATE TYPE [dbo].[RoomTableType] AS TABLE(
    [RoomId] [int] NULL,
    [PropertyId] [int] NULL,
    [RoomName] [varchar](120) NULL,
    [Url] [varchar](max) NULL
)

Sample data

USE MyDb

DECLARE @roomTableType as RoomTableType

INSERT INTO @roomTableType ([RoomName], [Url])
VALUES  ('Room 1', 'Url1'), ('Room 1', 'Url2'),     
        ('Room 2', 'Url3'), ('Room 2', 'Url4'),
        ('Room 3', 'Url5'), ('Room 3', 'Url6'),     
        ('Room 3', 'Url7')

EXEC dbo.phsp_CreatePropertyRooms @roomTableType, 1212

The result

Rooms

RoomId  PropertyId  RoomName    RoomDescription RoomLength  RoomWidth
---------------------------------------------------------------------
1259    1212        Room 3      test            1.00        1.00
1258    1212        Room 2      test            1.00        1.00
1257    1212        Room 1      test            1.00        1.00

RoomPhotoUrls

RoomPhotoUrlsId RoomId  Url
----------------------------
1290            1257    Url7
1289            1257    Url6
1288            1257    Url5
1287            1257    Url4
1286            1257    Url3
1285            1257    Url2
1284            1257    Url1

My stored procedure

USE MyDb
GO

/****** Object:  StoredProcedure [dbo].[phsp_CreatePropertyRooms]    Script Date: 21/02/2018 23:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Tony Simpson
-- Create date: 04/12/17
-- Description: Create the rooms and images associated
-- =============================================
ALTER PROCEDURE [dbo].[phsp_CreatePropertyRooms]
    @roomsTableType [dbo].[RoomTableType] READONLY,
    @propertyId INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @distinctRooms TABLE ([RoomId] int, [RoomName] varchar(120), [RoomDescription] varchar(MAX), [RoomLength] decimal, [RoomWidth] decimal)
    DECLARE @insertedRooms TABLE ([RoomId] int, [RoomName] varchar(120), [RoomDescription] varchar(MAX), [RoomLength] decimal, [RoomWidth] decimal)
    DECLARE @roomsTableTypeCopy TABLE ([RoomId] int, [RoomName] varchar(120), [RoomDescription] varchar(MAX), [RoomLength] decimal, [RoomWidth] decimal, [Url] Varchar(MAX))

    --get distinct rooms
    INSERT INTO  @distinctRooms (RoomName, RoomDescription, RoomLength, RoomWidth)
        SELECT DISTINCT
                 r.RoomName
                ,r.RoomDescription
                ,r.RoomLength
                ,r.RoomWidth
        FROM     @roomsTableType r

    --insert into rooms table and save details in @insertedRooms
    INSERT  INTO Rooms (RoomName, RoomDescription, RoomLength, RoomWidth, PropertyId)
    OUTPUT  inserted.RoomId, inserted.RoomName, inserted.RoomDescription, inserted.RoomLength, inserted.RoomWidth INTO @insertedRooms
        SELECT  RoomName,
                RoomDescription,
                RoomLength,
                RoomWidth,
                @propertyId
        FROM    @distinctRooms

    --populate @propertyTableTypeCopy so it can be updated
    INSERT INTO @roomsTableTypeCopy (
        [RoomId]
        ,[RoomName]
        ,[RoomDescription]
        ,[RoomLength]
        ,[RoomWidth]
        ,[url])
        SELECT  RoomId
                ,RoomName
                ,RoomDescription
                ,RoomLength
                ,RoomWidth
                ,[Url]
        FROM    @roomsTableType

    --update rooms in @propertyTableTypeCopy with room id
    UPDATE  @roomsTableTypeCopy
    SET     [RoomId] = SR.RoomId
    FROM    @insertedRooms SR
    CROSS APPLY (
        SELECT  [RoomId]
                ,[RoomName]
                ,[RoomDescription]
                ,[RoomLength]
                ,[RoomWidth]
        FROM    @insertedRooms
        WHERE   SR.RoomName = RoomName
            AND SR.RoomLength = RoomLength
            AND SR.RoomDescription = RoomDescription
            AND SR.RoomWidth = RoomWidth
    ) AS r;

    --add to table
    INSERT INTO RoomPhotoUrls (RoomId, [Url])
    SELECT [RoomId], [url]
    FROM @roomsTableTypeCopy
END