Yes, but you'll need to add error handling. Since you are using 2005, you should do the transaction inside a TRY/CATCH block. Here's an example from something I wrote last year (code changed to protect proprietary stuff): BEGIN TRY BEGIN TRAN INSERT INTO ... DELETE ... COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN IF ERROR_NUMBER() = 1205 AND @retries < @maxRetries -- 1205 is deadlock error BEGIN SET @retries = @retries + 1 WAITFOR DELAY '00:00:10' END ELSE -- some other error or done retrying BEGIN SELECT @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY(), @errorState = ERROR_STATE(); RAISERROR (@errorMessage, @errorSeverity, @errorState); RETURN END END CATCH
I'm handling deadlocks on this one, but the point really is that it is properly handling errors. When you use a transaction, you have to include COMMIT when there aren't any errors and then ROLLBACK when there are errors.In 2000, we didn't have TRY/CATCH so the code would be different. We'd have to check @@ERROR after each DML statement to determine whether to continue, COMMIT, or ROLLBACK.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog 
one stored proc with two execute statements issue
Posted - 2010-04-28 : 13:13:36
