CREATE PROC dbo.spAddSectionMenuItem
	@SectionID AS smallint,
	@MenuType AS char(5),
	@LinkRoute AS varchar(50),
	@LinkName AS varchar(50),
	@LinkTitle AS varchar(250)
AS

SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @ErrorNum int

BEGIN TRANSACTION

IF NOT EXISTS (SELECT 1 FROM dbo.SectionMenuItems WHERE SectionID = @SectionID and MenuType = @MenuType)
BEGIN
	INSERT INTO dbo.SectionMenuItems (SectionID, MenuType, LinkRoute, LinkName, LinkTitle)
	VALUES (@SectionID, @MenuType, @LinkRoute, @LinkName, @LinkTitle);
	
	SET @ErrorNum = @@ERROR;
END

if (@ErrorNum = 0)
BEGIN
	COMMIT
END
ELSE
BEGIN
	ROLLBACK
END

RETURN @ErrorNum

CREATE PROC dbo.spAddSectionMenuItem
	@SectionID AS smallint,
	@MenuType AS char(5),
	@LinkRoute AS varchar(50),
	@LinkName AS varchar(50),
	@LinkTitle AS varchar(250)
AS

SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @ErrorNum int

BEGIN TRY
    BEGIN TRANSACTION

    IF NOT EXISTS (SELECT 1 FROM dbo.SectionMenuItems WHERE SectionID = @SectionID and MenuType = @MenuType)
    BEGIN
	    INSERT INTO dbo.SectionMenuItems (SectionID, MenuType, LinkRoute, LinkName, LinkTitle)
	    VALUES (@SectionID, @MenuType, @LinkRoute, @LinkName, @LinkTitle);
	
	    RETURN 0;
    END

    COMMIT
END TRY

BEGIN CATCH
    RETURN @@ERROR;

    ROLLBACK
END CATCH

CREATE PROC dbo.spAddSectionMenuItem
	@SectionID AS SMALLINT,
	@MenuType AS CHAR(5),
	@LinkRoute AS VARCHAR(50),
	@LinkName AS VARCHAR(50),
	@LinkTitle AS VARCHAR(250)
AS

SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @ErrorNum int
BEGIN TRY
	BEGIN TRAN
	
    IF NOT EXISTS (SELECT 1 FROM dbo.SectionMenuItems WHERE SectionID = @SectionID and MenuType = @MenuType)
    BEGIN
	    INSERT INTO dbo.SectionMenuItems (SectionID, MenuType, LinkRoute, LinkName, LinkTitle)
	    VALUES (@SectionID, @MenuType, @LinkRoute, @LinkName, @LinkTitle);
	
	    RETURN 0;
    END
    	
	COMMIT
	SET @ErrorNum = 0;

END TRY
BEGIN CATCH
	IF (XACT_STATE()) = -1
	BEGIN
	    ROLLBACK TRAN; -- cannot commit, you can only roll back
	END
	ELSE IF  (XACT_STATE()) = 1
		BEGIN
			COMMIT TRAN; -- You can commit, but you can roll back if you choose to.
		END
	-- else nothing to commit
	SET @ErrorNum = 1;
END CATCH 
RETURN @ErrorNum;

quote:

---

Originally posted by sunitabeck

If you want to nest TRY/CATCH blocks along with corresponding ROLLBACK's you have to be careful. SQL Server really does not support nested transactions. So when you rollback, it rolls back all the operations from the outermost BEGIN TRAN.

SQL Server has a concept of savepoints which allows you to do partial rollbacks. I don't have much experience with it. May be someone else can give you more insight into it. http://msdn.microsoft.com/en-us/library/ms378414(v=sql.100).aspx

---

quote:

---

Originally posted by Lamprey

Not necessarily. You can raise an error from the CATACH block that will bubble up.

Also, there is no need for the transaction in the sample you showed because the action is atomic. But, if you are working on a more genral template, then that's cool.

---

quote:

---

Originally posted by Eagle_f90
[br}How would I raise the error so the calling proc will know?

---

quote:

---

Just so I understand you right, since it is such a basic insert there is no need for the transaction calls? The insert will either succeed or fail.

---