USE [Havsdrottningen]
GO
/****** Object:  StoredProcedure [dbo].[AddNewSalongWithChairs]    Script Date: 06/29/2011 19:42:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AddNewSalongWithChairs]
    @Name nvarchar(50)
    , @Description nvarchar(50)
    , @ChairList VARCHAR(8000)

AS
    BEGIN TRY
        BEGIN TRAN

            IF @Name = '' OR @Name = NULL
                OR @Description = '' OR @Description = NULL
                OR @ChairList = '' OR @ChairList = NULL
            BEGIN
                RAISERROR (N'All fields need to have data'
                    , 15 -- Error level between 11 to 19 will make script go to CATCH.
                    , 1)
            END

            INSERT INTO Salong
                VALUES(@name, @Description)

            DECLARE @SalongID INT;
            DECLARE @COUNT INT;
            DECLARE @numofseats INT;
            DECLARE @spot INT;
            DECLARE @seatID INT;
            DECLARE @seatNum INT;

            SET @SalongID = CAST(SCOPE_IDENTITY() AS INT)
            SET @COUNT = 0
            SET @seatNum = 0;

            WHILE @ChairList <> '' --An loop to go true an list of numbers seperated with an ,
            BEGIN
                SET @spot = CHARINDEX(',', @ChairList)
                IF @spot>0
                    BEGIN --Find the first , and take the number before it.
                        SET @numofseats = CONVERT(INT, LEFT(@ChairList, @spot-1))
                        SET @ChairList = RIGHT(@ChairList, LEN(@ChairList)-@spot)
                    END
                ELSE
                    BEGIN --If there is no , left.
                        SET @numofseats = CONVERT(INT, @ChairList)
                        SET @ChairList = ''
                    END

                IF @numofseats > 999 OR @numofseats <= 0
                BEGIN
                    RAISERROR (N'Number of seats per row can only be between 1 and 999'
                        , 15 -- Error level between 11 to 19 will make script go to CATCH.
                        , 1)
                END

                IF @COUNT >= 99
                BEGIN
                    RAISERROR (N'You cannot have more then 99 rows of seats.'
                        , 15 -- Error level between 11 to 19 will make script go to CATCH.
                        , 1)
                END

                INSERT INTO SalongRow
                    VALUES(@SalongID, @COUNT+1);

                SET @seatID = CAST(SCOPE_IDENTITY() AS INT);
                SET @COUNT = @COUNT+1;

                WHILE @numofseats > 0
                BEGIN
                    INSERT INTO dbo.Seat
                        VALUES(@seatID, @seatNum+1);
                    SET @numofseats = @numofseats-1;
                    SET @seatNum = @seatNum + 1;
                END

            END

            ;SELECT @SalongID AS ID;
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        ;SELECT ERROR_MESSAGE();
        ROLLBACK TRAN
    END CATCH