Multiple Tasks In Stored Procedure

I have put together the stored-procedure below to carry out update, delete and insert queries in one visit. The code has been pieced together from the pages listed at the bottom. I pass the procedure three XML strings and after testing it a few times it seems to work fine. I’m fairly new to stored procedures though, so I was hoping someone would answer these questions:


1. Is this an acceptable way to do this? Can you foresee any problems?
2. I want to make this an ‘all-or-nothing’ event, i.e. if any part of the procedure fails, it must all fail. How would I achieve that?
3. I want to know in my calling code what the result is. I’ve used output parameters before, but I’m unsure how to combine one with 2 above.

Sorry this is a long script, but I’ve removed most of the column names and values to shorten it. Thanks in advance.CREATE PROCEDURE amendPageRecords
@PagesToUpdate xml,
@PagesToDelete xml,
@PagesToInsert xml

AS
BEGIN

-- UPDATING RECORDS ------------------------------------------------------------
DECLARE @UpdateTable TABLE (PageID int, PageType varchar(10))

INSERT INTO @UpdateTable (PageID, PageType)

SELECT PageID = UpdatePages.Item.value('@PageID', 'int'),
PageType = UpdatePages.Item.value('@PageType', 'varchar(10)')
FROM @PagesToUpdate.nodes('Pages/Page') AS UpdatePages(Item)

UPDATE page
SET page.page_type = UP.PageType
FROM page INNER JOIN @UpdateTable UP ON page.page_id = UP.PageID

-- DELETING RECORDS ------------------------------------------------------------
DECLARE @DeleteTable TABLE (PageID int)

INSERT INTO @DeleteTable (PageID)
SELECT PageID = DeletePages.Item.value('@PageID', 'int')
FROM @PagesToDelete.nodes('Pages/Page') AS DeletePages(Item)

DELETE FROM page
FROM page INNER JOIN @DeleteTable DP ON page.page_id = DP.PageID

-- INSERTING RECORDS -----------------------------------------------------------
DECLARE @InsertTable TABLE (SiteID int, PageType varchar(10))

INSERT INTO @InsertTable (SiteID, PageType)

SELECT SiteID = InsertPages.Item.value('@SiteID', 'int'),
PageType = InsertPages.Item.value('@PageType', 'varchar(10)')
FROM @PagesToInsert.nodes('Pages/Page') AS InsertPages(Item)

INSERT INTO page
SELECT SiteID, PageType
FROM @InsertTable

END
GOCode taken from:http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspxhttp://www.eggheadcafe.com/articles/20030627c.asphttp://www.sommarskog.se/arrays-in-sql-2005.html