begin transaction
UPDATE A
SET FUL_CENTER_FLG = 'Y'
FROM S_ORG_EXT A, EIM_ACCOUNT B
WHERE B.LOC = A.LOC
and not exists
(select * from S_EVT_ACT C where C.TARGET_OU_ID = A.ROW_ID )
--check the data and commit / rollback accordingly
Re: Classic Nest SP with Transaction Question ... PMFJI, but if your child proc is using an explicit tran, then it can be coded as follows: create proc dbo.ChildProc as set nocount on declare @trancount int set @trancount = @@TRANCOUNT if @trancount> 0 begin tran ChildProcTran else save tran ChildProcTran /* ... Do some stuff */ if @@ERROR> 0 begin raiserror rollback ChildProcTran return end ...commit tran go ... I believe I'm having the same issue as Chad with nested stored procedures inside a transaction.... (microsoft.public.sqlserver.programming)
Re: SQL SERVER Rollback Problems ... It will not be fully committed until you issue a COMMIT TRAN. ... If your sp had an error or was manually stopped the transaction is still ... issue the ROLLBACK ALL operations are rolled back up until that first BEGIN ... > execution.... (microsoft.public.sqlserver.programming)
Re: how to roll back ... You can only use rollback within a transaction block.... If you commit a ...COMMIT TRAN, then ROLLBACK TRAN to ... (microsoft.public.sqlserver.server)
Re: help - transaction control ... transaction to successfully commit before any of the inner ones are ... If a Rollback is issued anywhere along the way ALL transactions ...ROLLBACK TRAN... > My understanding of the way that transactions nest> is that I should be able to have each SP> begin and commit a transaction within itself,> and if the SP happens to be called from some context> that has begun a transaction, then the SP would participate> in that transaction context. ... (microsoft.public.sqlserver.programming)
Re: help - transaction control ...>transaction to successfully commit before any of the ... If a Rollback is issued anywhere along the ... > ROLLBACK TRAN... (microsoft.public.sqlserver.programming)
