SQL Try/Catch

This stored procedure formats and re-throws the error:

/****** Oggetto:  StoredProcedure [dbo].[sys_RethrowError_sp]    Data script: 03/12/2009 15:41:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sys_RethrowError_sp] AS
	
-- SE NON CI SONO ERRORI, ESCE
IF ERROR_NUMBER() IS NULL RETURN


-- VARIABILI PER MEMORIZZARE L'ERRORE
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorLine INT
DECLARE @ErrorProcedure NVARCHAR(200)


-- IMPOSTA IL MESSAGGIO DI ERRORE
SET	@ErrorSeverity = ERROR_SEVERITY()
SET	@ErrorLine = ERROR_LINE()
SET	@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')


SET @ErrorMessage = N'Procedura: %s, Linea: %d, Messaggio di errore: ' + CHAR(10) + ERROR_MESSAGE()


-- RILANCIA L'ERRORE
RAISERROR 
(
	@ErrorMessage, 
	@ErrorSeverity, 
	1,               
	@ErrorProcedure, -- parameter: original error procedure name.
	@ErrorLine       -- parameter: original error line number.
)


GO

And here's a an example using the previous stored procedure:

/****** Oggetto:  StoredProcedure [dbo].[x_test_trycatch_sp]    Data script: 03/12/2009 15:41:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[x_test_trycatch_sp]
as

set nocount on

create table #tab
(
	Nome nvarchar(10) not null primary key,
	Valore int null
)


begin try
	begin transaction
		insert #tab(nome, valore) values ('a', 1)
		insert #tab(nome, valore) values ('b', 2)
		insert #tab(nome, valore) values ('c', 3)
		-- causo l'errore
		insert #tab(nome, valore) values ('b', 2)
	commit
end try

begin catch
	rollback
	exec sys_RethrowError_sp
end catch


select * from #tab