I think this does basically the same thing but runs in about 6 seconds on my SQL 2012 box.

Declare @CompID int = 1050

,@ProcessingDate DATETIME = GETDATE();

BEGIN

-- Check if any data available to be processed

IF EXISTS (

SELECT 1

FROM Comp_Detail

WHERE CompID = @CompID

AND coalesce(processStatus, 0) = 0

)

BEGIN

BEGIN TRY

BEGIN TRANSACTION;

-- Aggregare the ProcessingAmount based on UserId

WITH AggregateData

AS (

SELECT UserId

,ProcessingAmount = SUM(COALESCE(TransferAmount, 0))

FROM Comp_Detail

WHERE CompID = @CompID

AND coalesce(processStatus, 0) = 0

GROUP BY UserId

)

--Do the Amount update

UPDATE UB

SET UB.Amount_Available = COALESCE(UB.Amount_Available, 0) + AD.ProcessingAmount

,UB.Amount_Pend = COALESCE(UB.Amount_Pend, 0) - AD.ProcessingAmount

,LastModDt = @ProcessingDate

FROM User_bank UB

INNER JOIN AggregateData AD ON UB.UserId = AD.UserId;

UPDATE a

SET processStatus = 1

,AmtTransferDate = @ProcessingDate

FROM Comp_Detail a

JOIN User_bank b ON a.Userid = b.Userid

WHERE CompID = @CompID AND coalesce(a.processStatus, 0) = 0 AND LastModDT = @ProcessingDate

COMMIT TRANSACTION

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE()

,@ErrorSeverity = ERROR_SEVERITY()

,@ErrorState = ERROR_STATE();

ROLLBACK TRANSACTION;

RAISERROR (

@ErrorMessage

,@ErrorSeverity

,@ErrorState

);

END CATCH;

END

END

A couple of notes:

- My logic is based on what I think is going on. Your original loop wouldn't run in a period of time I was willing to wait for to check the final results.

- This bit in the WHERE clause (both of my queries)

coalesce(a.processStatus, 0) = 0

is not SARGable, so that could be improved on by making the processStatus column NOT NULL.

- Indexing might also help a bit but since 6 seconds is already quite a bit faster than the 24 I'm hearing above, maybe that will take care of it for you. Assuming my logic is correct that is.