Uri Dimant
3/28/2007 6:02:00 AM
BigO
Take a look at below example
CREATE PROCEDURE BigOne
AS
DECLARE @err integer
BEGIN TRANSACTION
EXEC @err = sp1
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp2
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp3
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp4
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACION
GO
"BigO" <pgouellette@fuse.net> wrote in message
news:1175054522.744407.273070@p15g2000hsd.googlegroups.com...
>I am writing a table load process that I'd Like to control by one main
> stored procedure. That main stored procedure executes multiple store
> procedures that each creates & inserts multiple rows into the SAME
> table.
> My logic is as follows: (Don't worry about the syntax of the SQL)
>
> Create Procedure MAIN
> As
> --------------------------------------------------------------------------------------------------------------------------------------------------
> DECLARES @Load_Status, @SP_Account_Level_Status, etc.
> --------------------------------------------------------------------------------------------------------------------------------------------------
> BEGIN TRANSACTION Monthly_Load_Transaction
>
> Execute SP_Insert_Monthly_Account_Level_Values
> IF @SP_Account_Level_Status = 0
> BEGIN
> SET @Load_Status = 1
> Execute SP_Insert_Monthly_Company_Level_Values
> IF SP_Company_Level_Status = 0
> BEGIN
> SET @Load_Status = 2
> Execute
> SP_Insert_Monthly_Consolidated_Level_Values
> IF @SP_Consolidated_Level_Status = 0
> SET @Load_Load_Status = 3
> ELSE --
> Insert_Monthly_Consolidated_Level_Values Failed
> BEGIN
> SET @Load_Status = -3
> Execute Log_Load_MSG 3
> END
> END
> ELSE -- Insert_Monthly_Company_Level_Values failed
> BEGIN
> SET @Load_Status = -2
> Execute Log_Load_ MSG 2
> END
> END
> ELSE -- Insert_Account_Level_Values failed
> BEGIN
> SET @Load_Status = -1
> Execute Log_Load_ MSG 1
> END
> END -- Monthly_Load_Transaction
> If @Load_Status = 3 -- Commit all Inserts
> BEGIN
> COMMIT Monthly_Load_Transaction
> Execute Log_Load_ MSG 4
> END
> ELSE - Rollback all Inserts
> BEGIN
> ROLLBACK Monthly_Load_Transaction
> Execute Log_Load_ MSG 5
> END
>
> Here's my question:
>
> If I do not perform any inserts using Begin Transactions in the
> "Insert" stored procedures or perform any COMMITS for any inserts into
> the table in any of the "Insert" store procedures, will the COMMIT or
> ROLLBACK work at the MAIN stored Procedure level for all the inserts
> performed in the "Insert" stored procedures?
>
> Or are commits implicitly performed when exiting each of the "Insert"
> stored procedures?
>