Sample Stored procedure with Try - Catch, Transaction

Here is the ready reference of the sample Stored procedure with Try - Catch, Transaction. It will boost your speed on writing stored procedures:

create procedure Sample_sp
(
@Error int out  
)
as
Begin
declare @trancount int;
set @trancount = @@trancount;
if @trancount = 0
Begin TRY
Begin Tran


if @trancount = 0
Begin
Set @Error = 1;  
commit Tran  
End
END TRY
BEGIN CATCH

Set @Error = 0;  
declare @error1 int, @message varchar(4000), @xstate int;
select @error1 = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback;
if @xstate = 1 and @trancount > 0
rollback ;

select 'ERROR:' +  cast(@error1 as nvarchar(10)) + ' ' + @message as ErrorMessage;

       
END CATCH
End

Happy Coding !!!