Stripe Payment Processing with Error Handling

advanced

Stripe processes millions of payments daily. Each payment requires multiple steps: validate card, check fraud score, deduct amount, create transaction record, update merchant balance. If any step fails, everything must rollback to prevent partial payments. T-SQL stored procedures with TRY/CATCH ensure atomic operations.

-- Payment processing procedure with error handling

CREATE PROCEDURE ProcessPayment

@PaymentID INT,

@CardToken VARCHAR(100),

@Amount DECIMAL(10,2),

@MerchantID INT,

@TransactionID INT OUTPUT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @FraudScore INT;

DECLARE @MerchantBalance DECIMAL(10,2);

BEGIN TRY

BEGIN TRANSACTION;

-- Step 1: Validate card and check fraud score

SELECT @FraudScore = fraud_score

FROM card_validations

WHERE card_token = @CardToken;

IF @FraudScore > 80

BEGIN

THROW 50001, 'High fraud risk - payment declined', 1;

END;

-- Step 2: Create transaction record

INSERT INTO transactions (payment_id, amount, status, created_at)

VALUES (@PaymentID, @Amount, 'processing', GETDATE());

SET @TransactionID = SCOPE_IDENTITY();

-- Step 3: Deduct from customer (external API call simulation)

-- In real system, this would call payment gateway

WAITFOR DELAY '00:00:01'; -- Simulate API delay

-- Step 4: Update merchant balance

UPDATE merchants

SET balance = balance + (@Amount * 0.97), -- 3% fee

total_transactions = total_transactions + 1

WHERE merchant_id = @MerchantID;

-- Step 5: Mark transaction as completed

UPDATE transactions

SET status = 'completed', completed_at = GETDATE()