How to make sure a record is only retrieved once
I'm assuming this is the following case:


You can look up coupon multiple times (example: verifying the coupon exist)
Once you use the coupon something the coupon becomes invalid 


Easiest way is to have two fields called isUsed int, ExpirationDate datetime
Then create two APIs that do the following:


CheckCoupon (coupon id)

This checks against the table for coupon id, ExpirationDate, isUsed. If the stored procedure returns 1, the coupon is valid, else invalid.

Create a stored procedure called usp_check_coupon

SELECT count(*) 
FROM Coupon
Where id = @id
and ExpirationDate &gt; [today's_date]
and IsUsed = 0

UseCoupon (coupon id)

Marks the coupon as used. Before updating IsUsed to 1, check to make sure IsUsed is 0. 

Create a stored procedure called usp_use_coupon

DECLARE @isused int =
    SELECT count(*) 
    FROM Coupon 
    Where id = @id
    and ExpirationDate &gt; [today's_date]
    and IsUsed = 0

IF (@isused == 1)
BEGIN
    UPDATE COUPON
    SET IsUsed = 1
    WHERE ID = @id

   SELECT 1 --Updated Successfully
END

SELECT 0 --Updated Failed



Then on the API, if usp_use_coupon returns 0, that means the coupon is used or expired. If 1, it has been processed.