DECLARE	@return_value Int

EXEC	@return_value = [dbo].[spAddNewSection]
		@UpdateType = N'Insert',
		@SectionTitle = N'ProcTest',
		@ShortName = N'ProcTest',
		@RouteName = N'ProcTest',
		@Synopsis = N'ProcTest',
		@SectionType = N'ProcTest',
		@PrimaryCat = N'ProcTest',
		@PrimaryCatPos = 0,
		@UpdaterName = N'ProcTest',
		@FileID = NULL,
		@FilePath = N'ProcTest',
		@FileName = N'ProcTest'

CREATE PROC dbo.spAddNewSection
    @UpdateType AS char(6),
    @SectionTitle AS varchar(150),
    @ShortName AS varchar(50),
    @RouteName AS varchar(15),
    @Synopsis AS varchar(max),
    @SectionType AS varchar(15),
    @PrimaryCat AS varchar(16),
    @PrimaryCatPos AS tinyint,
    @SecondaryCat AS varchar(16) = NULL,
    @SecondaryCatPos AS tinyint = NULL,
    @SectionID AS tinyint = NULL,
    @UpdaterName AS varchar(50),
    @FileID AS int = NULL,
    @FilePath AS varchar(250) = NULL,
    @FileName AS varchar(50) = NULL
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION

        --Proccess new section
        IF (@UpdateType = 'Insert')
        BEGIN
            --Insert new section information
            INSERT INTO dbo.tblSectionInfo (SectionTitle, ShortName, RouteName, Synopsis, SectionType)
            VALUES (@SectionTitle, @ShortName, @RouteName, @Synopsis, @SectionType);

            --Get the uniquie ID of the new section
            SELECT @SectionID = SCOPE_IDENTITY();

            --Insert primary and secondary category information
            INSERT INTO dbo.tblSectionCategories (SectionID, CategoryName, MenuPosition)
            SELECT @SectionID, @PrimaryCat, @PrimaryCatPos
            UNION ALL
            SELECT @SectionID, @SecondaryCat, @SecondaryCatPos
            WHERE @SecondaryCat IS NOT NULL;

            --Log the new section
            INSERT INTO dbo.tblSiteUpdates (UpdatedSectionID, UpdatedInfoSectionName, UpdatedItemName, UpdateType, UpdatedBy)
            VALUES (@SectionID, 'Sections', @ShortName, 'New', @UpdaterName)
        END
        ELSE IF (@UpdateType = 'Update') --Process a section update
        BEGIN
            --Update table
            UPDATE dbo.tblSectionInfo
            SET SectionTitle = @SectionTitle,
                ShortName = @ShortName,
                RouteName = @RouteName,
                Synopsis = @Synopsis,
                SectionType = @SectionType
            WHERE SectionID = @SectionID;

            --Remove category assignments
            DELETE FROM dbo.tblSectionCategories
            WHERE SectionID = @SectionID;

            --Add back category assignments
            INSERT INTO dbo.tblSectionCategories (SectionID, CategoryName, MenuPosition)
            SELECT @SectionID, @PrimaryCat, @PrimaryCatPos
            UNION ALL
            SELECT @SectionID, @SecondaryCat, @SecondaryCatPos
            WHERE @SecondaryCat IS NOT NULL;

            --Log section update
            INSERT INTO dbo.tblSiteUpdates (UpdatedSectionID, UpdatedInfoSectionName, UpdatedItemName, UpdateType, UpdatedBy)
            VALUES (@SectionID, 'Sections', @ShortName, 'Update', @UpdaterName)
        END

        IF (@FilePath != NULL)
        BEGIN
            EXEC @FileID = dbo.spFiles @UpdateType, @FileID, @FilePath, @FileName;

            Update dbo.SectionInfo
            SET FilePathID = @FileID
            WHERE SectionID = @SectionID;
        END

    COMMIT
END TRY

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState);

    IF ((XACT_STATE()) = -1)
    BEGIN
        ROLLBACK;
    END
    ELSE IF ((XACT_STATE()) = 1)
    BEGIN
        COMMIT;
    END

END CATCH