Stored procedure transaction handling

[fifelo]

I am trying to create a stored procedure to handle some typical transaction stuff i.e. mostly a retry loop. Its a long story but basically I want to make it so this stored procedure has the least chance of failing. The program using it can either execute one SQL statement or call a stored procedure. It has been failing occasionally on the insert, so I have decided to write a stored procedure which it will call ( the stored procedure will handle 10 retries on the transaction ) I can not change the way in which the program works. I have written the stored procedure, and I think its basically sound although I want to see if anyone out there has some input as to why this may or may not be a very terrible idea. Also is there a way to sleep for maybe .25 seconds between retries? Below is the stored procedure code. Thanks in advance to any advice I might recieve.

CREATE PROCEDURE insert_fault
@fault_id int,
@time_started datetime

AS
--This stored procedure is used to log a new fault starting, and to double check that there weren't any other same faults that were
--started and never completed

--The logic of this is basically as follows
--There should only ever be one distinct fault_id in this table at any given time ( it should be waiting for the fault to stop hence
-- updating its time_stoppped)
--I could imagine under some strange cirumstatnces with locking or whatever that we could have 2 conditions arise
--A. we have a fault that has both a correct time_started, and time_stopped but for one reason or anther never got moved to
-- the fault_log table. This should never happen, but if it does we got that covered
--B. we have a fault that never got updated to time_stoppped and moved ( in this case we update the time_stopped to same as
-- time_started which denotes the fact that we don't really know when it stopped )

DECLARE @@generalerror bit
DECLARE @@retrycount int

SET @@generalerror = 1
SET @@retrycount = 0

-- we will loop 10 times trying to do this transaction, if we finally can't do it... we give up

WHILE ( @@retrycount < 10 AND @@generalerror = 1 )
SET @@generalerror = 0
BEGIN

BEGIN TRANSACTION
-- case A
INSERT INTO fault_log(fault_id, time_started, time_stopped)
SELECT fault_id,time_started,time_stopped
FROM fault_log_temp
WHERE fault_id = @fault_id AND
time_stopped IS NOT NULL
IF @@error <> 0
BEGIN
SET @@generalerror = 1
END
-- case B
INSERT INTO fault_log(fault_id, time_started, time_stopped)
SELECT fault_id,time_started,time_started
FROM fault_log_temp
WHERE fault_id = @fault_id AND
time_stopped IS NULL
IF @@error <> 0
BEGIN
SET @@generalerror = 1
END

DELETE
FROM fault_log_temp
WHERE fault_id = @fault_id

IF @@error <> 0
BEGIN
SET @@generalerror = 1
END

--here we insert the fault_log_temp record
INSERT INTO fault_log_temp(fault_id, time_started) VALUES ( @fault_id, @time_started )

IF @@error <> 0
BEGIN
SET @@generalerror = 1
END

IF @@generalerror = 1
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET @@retrycount = @@retrycount + 1
END
GO