quote:

---

Originally posted by sunitabeck

If I understood your description correctly, it would not rollback both procs. A rollback will rollback to the outermost begin transaction assuming there are no savepoints. So all the statements before the begin transaction in the first proc would still stay and any statements after the rollback statement would get executed as well.

---

BEGIN TRANSACTION
INSERT INTO dbo.tblTable (COLUMNS) VALUES (VALUES)
UPDATE dbo.tblTable2
SET COLUMN = VALUE

EXEC dbo.spAnotherProcThatFails

COMMIT

quote:

---

Originally posted by visakh16

yep. if it fails in current way its return it will cause the insert/update also to be rolled back

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

CREATE PROCEDURE dbo.TestR
AS
	SELECT 1.0/0.0;
GO

-- ALL STATEMENTS GET EXECUTED
SET XACT_ABORT OFF

BEGIN TRAN
	SELECT @@TRANCOUNT
	EXEC dbo.TestR;
	SELECT @@TRANCOUNT
COMMIT
	SELECT @@TRANCOUNT

-- NOTHING AFTER THE DIVIDE BY ZERO GETS EXECUTED

SET XACT_ABORT ON

BEGIN TRAN
	SELECT @@TRANCOUNT
	EXEC dbo.TestR; -- nothing beyond this is executed.
	SELECT @@TRANCOUNT
COMMIT
	SELECT @@TRANCOUNT

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 dbo.spFiles @UpdateType, @FileID, @FilePath, @FileName;
        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

CREATE PROC dbo.spFiles
    @UpdateType AS char(6),
    @FileID AS int,
    @FilePath AS varchar(250),
    @Name AS varchar(50)
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

    --Remove existing file entries.
    IF (@UpdateType = 'Update' OR @UpdateType = 'Delete')
    BEGIN
        DELETE FROM dbo.tblFiles
        WHERE FileID = @FileID;
    END

    --Process a new file.
    IF (@UpdateType = 'Insert' OR @UpdateType = 'Update')
    BEGIN
        INSERT INTO dbo.tblFiles (FilePath, Name)
        VALUES (@FilePath, @Name);

        --Get the FileID and return it to the calling proc.
        SELECT @FileID = SCOPE_IDENTITY();
    END
    RETURN @FileID;
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