Hi everybody,

I'm trying to figure out if I'm using a right concept with SP, transactions and error handling.

Comming from VFP background if you want to update multiple tables you usually wrap it in transactions.

Here is what I put right now in the CATCH block in SP, but like to understand if I'm correct :

BEGIN CATCH    
	   DECLARE @ErrorSeverity int, @ErrorNumber int, 
			@ErrorMessage nvarchar(4000), @ErrorState int, 
			@ErrorLine int, @ErrorProc nvarchar(200)
		-- Grab error information from SQL functions
		SET @ErrorSeverity = ERROR_SEVERITY()
		SET @ErrorNumber = ERROR_NUMBER()
		SET @ErrorMessage = ERROR_MESSAGE()
		SET @ErrorState = ERROR_STATE()
		SET @ErrorLine = ERROR_LINE()
		SET @ErrorProc = ERROR_PROCEDURE()
		SET @ErrorMessage = 'Problem updating person''s information.' + CHAR(13) + 
			'SQL Server Error Message is: ' + CAST(@ErrorNumber AS VARCHAR(10)) +
					' in procedure: ' + @ErrorProc + 
					' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) +
					' Error text: ' + @ErrorMessage 

		-- Not all errors generate an error state, to set to 1 if it's zero
		IF @ErrorState = 0
			SET @ErrorState = 1
		
		-- If the error renders the transaction as uncommittable or we have open transactions, we may want to rollback
		IF @@TRANCOUNT > 0
            BEGIN
            --print 'Rollback transaction' 
			  ROLLBACK TRANSACTION
            END

       RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState, @ErrorNumber) 
  END CATCH

After reading some related articles in BOL the question just seems to be more and more complex to me...

Thanks a lot in advance for the explanations.