Error tracking example - SQL coding

[admin]

--Create Table TestRollBack1 (Id int)
--Create Table TestRollBack2 (Id int)
--Create Table TestRollBack3 (Id int)

IF EXISTS(SELECT 1 FROM SYSOBJECTS
WHERE NAME = 'cspTESTROLLBACK1'
AND TYPE = 'p')
DROP PROCEDURE cspTESTROLLBACK1
GO
CREATE PROCEDURE cspTESTROLLBACK1 @ID INT
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO TESTROLLBACK1 VALUES(@ID)
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
INSERT INTO ERROR_TRACK (
ERROR_NUMBER
, ERROR_SEVERITY
, ERROR_STATE
, ERROR_LINE
, ERROR_MESSAGE
, ERROR_PROCEDURE
, CRETED_DATE )
SELECT
ERROR_NUMBER()
, ERROR_SEVERITY()
, ERROR_STATE()
, ERROR_LINE ()
, ERROR_MESSAGE()
, ERROR_PROCEDURE()
, GETDATE()
END CATCH
END
GO
--------------------''--------------------''--------------------
IF EXISTS(SELECT 1 FROM SYSOBJECTS
WHERE NAME = 'cspTESTROLLBACK2'
AND TYPE = 'p')
DROP PROCEDURE cspTESTROLLBACK2
GO
CREATE PROCEDURE cspTESTROLLBACK2 @ID INT
AS
BEGIN
BEGIN TRY
BEGIN TRAN
EXEC cspTESTROLLBACK1 @ID
INSERT INTO TESTROLLBACK2 VALUES(@ID)
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
INSERT INTO ERROR_TRACK (
ERROR_NUMBER
, ERROR_SEVERITY
, ERROR_STATE
, ERROR_LINE
, ERROR_MESSAGE
, ERROR_PROCEDURE
, CRETED_DATE )
SELECT
ERROR_NUMBER()
, ERROR_SEVERITY()
, ERROR_STATE()
, ERROR_LINE ()
, ERROR_MESSAGE()
, ERROR_PROCEDURE()
, GETDATE()
END CATCH
END
GO
--------------------''--------------------''--------------------
IF EXISTS(SELECT 1 FROM SYSOBJECTS
WHERE NAME = 'cspTESTROLLBACK3'
AND TYPE = 'p')
DROP PROCEDURE cspTESTROLLBACK3
GO
CREATE PROCEDURE cspTESTROLLBACK3 @ID1 VARCHAR(10), @ID2 VARCHAR(10), @ID3 VARCHAR(10)
AS
BEGIN
BEGIN TRY
BEGIN TRAN
EXEC cspTESTROLLBACK1 @ID1
EXEC cspTESTROLLBACK2 @ID2
INSERT INTO TestRollBack3 VALUES (@ID3)
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
INSERT INTO ERROR_TRACK (
ERROR_NUMBER
, ERROR_SEVERITY
, ERROR_STATE
, ERROR_LINE
, ERROR_MESSAGE
, ERROR_PROCEDURE
, CRETED_DATE )
SELECT
ERROR_NUMBER()
, ERROR_SEVERITY()
, ERROR_STATE()
, ERROR_LINE ()
, ERROR_MESSAGE()
, ERROR_PROCEDURE()
, GETDATE()
END CATCH
END
--------------------''--------------------''--------------------
EXEC cspTESTROLLBACK3 '1','1','1'
GO
SELECT * FROM TESTROLLBACK1
SELECT * FROM TESTROLLBACK2
SELECT * FROM ERROR_TRACK
GO
DELETE FROM TESTROLLBACK1
DELETE FROM TESTROLLBACK2
DELETE FROM ERROR_TRACK
GO