T-SQL stored procedures to .NET services: return codes, NULLs and hidden writes

1CREATE PROCEDURE dbo.usp_ApplyCredit
2  @CustomerId INT,
3  @Amount     MONEY
4AS
5BEGIN
6  SET NOCOUNT ON;
7  DECLARE @Limit MONEY, @Used MONEY;
8  SELECT @Limit = CreditLimit, @Used = CreditUsed
9    FROM dbo.Customer
10   WHERE CustomerId = @CustomerId;
11  IF @@ROWCOUNT = 0 RETURN 1;
12  IF @Used + @Amount > @Limit
13  BEGIN
14    INSERT INTO dbo.CreditHold (CustomerId, Amount, HeldAt)
15    VALUES (@CustomerId, @Amount, GETDATE());
16    RETURN 2;
17  END
18  UPDATE dbo.Customer
19     SET CreditUsed = CreditUsed + @Amount
20   WHERE CustomerId = @CustomerId;
21  RETURN 0;
22END

1{
2  "procedure": "dbo.usp_ApplyCredit",
3  "source": "usp_ApplyCredit.sql:1-22",
4  "result": {
5    "0": "CREDIT_APPLIED",
6    "1": "CUSTOMER_NOT_FOUND",
7    "2": "OVER_LIMIT_HOLD_RECORDED"
8  },
9  "decision": {
10    "hitPolicy": "FIRST",
11    "rules": [
12      { "when": "customer missing", "then": 1 },
13      { "when": "used + amount > limit", "then": 2 },
14      { "when": "otherwise", "then": 0 }
15    ]
16  },
17  "reads": ["dbo.Customer.CreditLimit", "dbo.Customer.CreditUsed"],
18  "writes": ["dbo.Customer.CreditUsed", "dbo.CreditHold"],
19  "types": { "Amount": "money, decimal(19,4)" },
20  "review": [
21    "CreditUsed NULL: limit check is UNKNOWN, skipped; CreditUsed stays NULL",
22    "No transaction or lock between SELECT and UPDATE: concurrent calls can both pass"
23  ]
24}