These are two frameworks that I can recommend.
Tst
http://tst.codeplex.com/
Testing SQL Server Code with TST
http://www.infoq.com/articles/tst-sql-server
tSQLt
http://tsqlt.org/
SQL Test (runner for tSQLt)
http://www.red-gate.com/products/sql-development/sql-test/
Update 1
By reading the answer, you might find a useful next dump.
TRY CATCH was introduced with SQL Server 2005 (and for this reason you should never look at an older one than in 2005). You can actually (repeat) throw an exception using the stored procedure specified in my dump, including the line number. In SQL Server 2012, they (finaly!) Introduced a cast as you mention that Tsql is a reliable language after 14 years.
So, here is my dump, I need to clean it someday so that its a friendlier copy.
SET XACT_ABORT ON SET CONCAT_NULL_YIELDS_NULL OFF DECLARE @message varchar ( max ) DECLARE @who varchar ( 255 ) set @who = OBJECT_NAME(@@PROCID) -- name of the currently executing sproc BEGIN TRY -- ====================================================================================== SET @message = 'HELLO' EXEC Log @who, @message .... -- ====================================================================================== SET @message = 'GOODBYE' EXEC Log @who, @message END TRY BEGIN CATCH -- ====================================================================================== --If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. --An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. -- http://msdn.microsoft.com/en-us/library/ms175976.aspx if XACT_STATE() = -1 rollback; -- ====================================================================================== SET @message = 'Rolling Back transaction if present' EXEC Log @who, @message -- Its important to rollback the transaction at the very start of the catch. -- Otherwise the records that are written to the log will also be roll backed. IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRAN END -- ====================================================================================== SET @message = 'Error Occured ' set @message = @message + ' ERROR_NUMBER() : ' + cast(ERROR_NUMBER() as varchar(max)) set @message = @message + ' ERROR_SEVERITY() : ' + cast(ERROR_SEVERITY() as varchar(max)) set @message = @message + ' ERROR_STATE() : ' + cast(ERROR_STATE() as varchar(max)) set @message = @message + ' ERROR_PROCEDURE() : ' +cast(ERROR_PROCEDURE() as varchar(max)) set @message = @message + ' ERROR_LINE() : ' + cast(ERROR_LINE() as varchar(max)) set @message = @message + ' ERROR_MESSAGE() : ' + cast(ERROR_MESSAGE() as varchar(max)) EXEC Log @who, @message exec usp_RethrowError END CATCH Error logging sproc and table CREATE PROCEDURE [dbo].[Log] ( @who varchar(255), @message varchar(max) ) AS SET XACT_ABORT ON SET CONCAT_NULL_YIELDS_NULL OFF INSERT INTO [ApplicationLog] ( [Date], [Level], [Logger], [Host], [Message] ) VALUES ( getDate(), 'INFO', @who, 'dummy', @message ) CREATE TABLE [dbo].[ApplicationLog] ( [Id] [int] IDENTITY(1, 1) NOT NULL, [Date] [datetime] NOT NULL, [Thread] [varchar](255) NULL, [Level] [varchar](50) NOT NULL, [Logger] [varchar](255) NOT NULL, [Host] [varchar](50) NOT NULL, [Message] [varchar](max) NOT NULL, [Exception] [varchar](max) NULL ) Rethrow an exception ALTER PROCEDURE [dbo].[usp_RethrowError] -- BOL contains a good example of that, there is a stored procedure called usp_RethrowError AS -- Return if there is no error information to retrieve. SET XACT_ABORT ON SET CONCAT_NULL_YIELDS_NULL OFF IF ERROR_NUMBER() IS NULL RETURN ; DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(200) ; -- Assign variables to error-handling functions that -- capture information for RAISERROR. SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ; -- Building the message string that will contain original -- error information. SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: ' + ERROR_MESSAGE() ; -- Raise an error: msg_str parameter of RAISERROR will contain -- the original error information. RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- parameter: original error number. @ErrorSeverity, -- parameter: original error severity. @ErrorState, -- parameter: original error state. @ErrorProcedure, -- parameter: original error procedure name. @ErrorLine-- parameter: original error line number. ) ;