sql server - SQL Procedure - Begin Transaction & Commit -

sql server - SQL Procedure - Begin Transaction & Commit -

hi writing procedure "pro_reallocate_block" within that, calling procedure "pro_remove_allocation". if set begin transaction main procedure "pro_reallocate_block", in case of error occurrence in main procedure revert sub procedure "pro_remove_allocation".

create procedure pro_reallocate_block( @blockid int, @blockname nvarchar(max) ) execute caller begin set xact_abort, nocount on; begin seek set transaction isolation level serializable begin transaction declare @allocatedblockid int exec pro_remove_allocation @blockid if((select count(blockid) blocks blockid=@blockid)>0) select * blocks blockid=@blockid else select * blocks bdefault=1 commit transaction set transaction isolation level read committed end seek begin grab if xact_state() <> 0 begin rollback transaction declare @errormessage nvarchar(4000); declare @errorseverity int; declare @errorstate int; select @errormessage = error_message(), @errorseverity = error_severity(), @errorstate = error_state(); raiserror (@errormessage, @errorseverity, @errorstate); end end grab end

sql-server sql-server-2008