How to Develop Programming Logic in SQL Server (T-SQL Banking)

Q: What is the core focus of the study 'How to Develop Programming Logic in SQL Server (T-SQL Banking)'?

Master variables, conditional branching, loops, cursors, table hints, and transaction handlers entirely inside SQL Server. Learn to think procedurally inside T-SQL.

Q: Which database schema is recommended to practice this study?

To practice the queries and concepts in this article, we recommend using the 'apexbank' database schema available in the SQLMarrow playground.

## 🚀 Developing Procedural Programming Logic in SQL Server (T-SQL) SQL is natively a **declarative language**—you define *what* dataset you want to retrieve, and the database optimizer decides *how* to compile the execution path. However, implementing real-world financial systems requires complex procedural logic. Calculating loan amortization schedules, compounding daily interest, enforcing ledger balances, or processing end-of-day batch files require conditional paths, variables, loops, and custom transactions. In Microsoft SQL Server, database engineers leverage **Transact-SQL (T-SQL)**—a powerful procedural extension of ANSI SQL. This comprehensive guide teaches you how to think like a software programmer within SQL Server, converting sequential programming models into highly optimized, premium database scripts. --- ### 📦 1. T-SQL Procedural – The Core Building Blocks When transitioning from pure queries to T-SQL database programming, you must master six fundamental elements: 1. **Variables:** Temporary memory registers declared using the `@` prefix to store intermediate values (e.g., `DECLARE @MyBalance DECIMAL(18,2) = 100.00;`). 2. **Conditional Branching (`IF...ELSE` / `CASE WHEN`):** Controlling execution flow depending on balance levels, credit ratings, or account status flags. 3. **Loops & Iteration (`WHILE`):** Repeating operations (such as compound interest calculations or month-by-month allocations) using the `WHILE` loops since T-SQL does not have a native `FOR` loop. 4. **Cursors:** Standard structures to evaluate rows one-by-one. Cursors should be used sparingly due to memory overhead; set-based statements are always preferred. 5. **Transactions (`BEGIN TRANSACTION` / `COMMIT` / `ROLLBACK`):** Ensuring database operations maintain strict ACID compliance. 6. **Exception Handling (`BEGIN TRY...BEGIN CATCH`):** Catching runtime errors (like numeric division-by-zero or lock timeouts) safely without causing sudden transaction abortion. --- ### 🏦 2. T-SQL Banking Logic Examples (Real Programming Patterns) Let's explore five core financial operations, studying how to build their algorithms using pure Transact-SQL. --- #### 📈 Example 1: Loan EMI Calculator & Amortization Schedule (Loop + Arithmetic) **The Business Requirement:** Given a principal loan amount, annual interest rate, and term duration, calculate the fixed monthly installment (EMI). Then, iterate month-by-month to split each payment into its interest component and principal component, logging each installment in a payment ledger. **The Logic Development Steps:** 1. **Define Inputs/Outputs:** Establish local variables with `@` prefix to hold monthly interest, EMI, running balance, and payment dates. 2. **Adjust Interest to Monthly Fraction:** Convert annual rates to monthly fractions: `@rMonthly = @RateAnnual / 100 / 12`. 3. **Execute the Fixed EMI Formula:** $$\text{EMI} = P \times \frac{r(1+r)^n}{(1+r)^n - 1}$$ 4. **Iterate Over Tenure (WHILE Loop):** Repeat the block while counter `@i <= @TenureMonths`. * Calculate interest due this month: `@Balance * @rMonthly`. * Subtract interest from the flat EMI to calculate the principal portion. * Subtract principal portion from the running balance to calculate closing balance. * Insert the payment schedule row. ##### T-SQL Implementation (Stored Procedure): ```sql CREATE PROCEDURE dbo.GenerateAmortization @LoanID INT, @Principal DECIMAL(18,2), @RateAnnual DECIMAL(18,4), @TenureMonths INT AS BEGIN SET NOCOUNT ON; DECLARE @rMonthly DECIMAL(18,6) = @RateAnnual / 100.0 / 12.0; DECLARE @EMI DECIMAL(18,2); DECLARE @Balance DECIMAL(18,2) = @Principal; DECLARE @InterestDue DECIMAL(18,2); DECLARE @PrincipalPart DECIMAL(18,2); DECLARE @DueDate DATE = GETDATE(); DECLARE @i INT = 1; -- Fixed Monthly EMI Formula calculation SET @EMI = @Principal * @rMonthly * POWER(1.0 + @rMonthly, @TenureMonths) / (POWER(1.0 + @rMonthly, @TenureMonths) - 1.0); WHILE @i <= @TenureMonths BEGIN SET @DueDate = DATEADD(month, 1, @DueDate); SET @InterestDue = @Balance * @rMonthly; SET @PrincipalPart = @EMI - @InterestDue; SET @Balance = @Balance - @PrincipalPart; INSERT INTO loan_payments ( loan_id, due_date, installment_amount, principal_part, interest_part, status ) VALUES ( @LoanID, @DueDate, ROUND(@EMI, 2), ROUND(@PrincipalPart, 2), ROUND(@InterestDue, 2), 'pending' ); IF @Balance [!NOTE] > For client-side sandboxes (like SQLite) that do not support stored procedures or custom loops, you can generate the exact same amortization table mathematically using an ANSI-compatible **Recursive CTE**: > > ```sql > WITH RecursiveAmortization(payment_no, opening_balance, payment, interest, principal, closing_balance) AS ( > -- Base Case: Month 1 > SELECT > 1, > principal_amount, > (principal_amount * (interest_rate / 12.0 / 100.0)) / (1 - POWER(1 + (interest_rate / 12.0 / 100.0), -12)), > principal_amount * (interest_rate / 12.0 / 100.0), > ((principal_amount * (interest_rate / 12.0 / 100.0)) / (1 - POWER(1 + (interest_rate / 12.0 / 100.0), -12))) - (principal_amount * (interest_rate / 12.0 / 100.0)), > principal_amount - (((principal_amount * (interest_rate / 12.0 / 100.0)) / (1 - POWER(1 + (interest_rate / 12.0 / 100.0), -12))) - (principal_amount * (interest_rate / 12.0 / 100.0))) > FROM loans WHERE loan_id = 456 > > UNION ALL > > -- Recursive Step: Months 2 through 12 > SELECT > payment_no + 1, > closing_balance, > payment, > closing_balance * (interest_rate / 12.0 / 100.0), > payment - (closing_balance * (interest_rate / 12.0 / 100.0)), > closing_balance - (payment - (closing_balance * (interest_rate / 12.0 / 100.0))) > FROM RecursiveAmortization, (SELECT interest_rate FROM loans WHERE loan_id = 456) AS l > WHERE payment_no ) > SELECT payment_no, ROUND(opening_balance, 2) as opening, ROUND(payment, 2) as emi, ROUND(interest, 2) as interest_portion, ROUND(principal, 2) as principal_portion, ROUND(closing_balance, 2) as remaining FROM RecursiveAmortization; > ``` --- #### 🔄 Example 2: Apply Monthly Interest to All Savings Accounts (Cursor + Loop) **The Business Requirement:** Iterate through all active savings accounts, calculate the interest earned for the month based on their daily balance rate, credit the interest directly, and log the double-entry accounting transaction in the general ledger. ##### T-SQL Implementation (Cursor & Stored Procedure): ```sql CREATE PROCEDURE dbo.CreditMonthlyInterest AS BEGIN SET NOCOUNT ON; DECLARE @AccountID INT; DECLARE @CurrentBalance DECIMAL(18,2); DECLARE @InterestRate DECIMAL(5,2); DECLARE @InterestEarned DECIMAL(18,2); -- EOMONTH returns last date; DAY gets the numeric day count (e.g. 30 or 31) DECLARE @DaysInMonth INT = DAY(EOMONTH(GETDATE())); DECLARE @DailyRate DECIMAL(18,8); -- 1. Declare the Cursor for savings accounts DECLARE AccountCursor CURSOR FOR SELECT a.account_id, a.current_balance, at.interest_rate FROM accounts a INNER JOIN account_types at ON a.type_id = at.type_id WHERE a.status = 'active' AND at.type_name = 'savings' AND at.interest_rate > 0; OPEN AccountCursor; -- 2. Fetch the first row FETCH NEXT FROM AccountCursor INTO @AccountID, @CurrentBalance, @InterestRate; -- 3. Loop through active rows WHILE @@FETCH_STATUS = 0 BEGIN SET @DailyRate = @InterestRate / 100.0 / 365.0; SET @InterestEarned = @CurrentBalance * @DailyRate * @DaysInMonth; -- Start safe atomic block per account BEGIN TRANSACTION; BEGIN TRY -- Insert transaction credit row INSERT INTO transactions (account_id, amount, transaction_type, description, transaction_date) VALUES (@AccountID, @InterestEarned, 'interest', 'Monthly interest credit', GETDATE()); -- Update master balance UPDATE accounts SET current_balance = current_balance + @InterestEarned, last_interest_date = GETDATE() WHERE account_id = @AccountID; -- Post dual accounting entries to the ledger INSERT INTO ledger_entries (entry_date, account_code, description, debit, credit) VALUES (GETDATE(), 'InterestExpense', 'Interest accrued for Account ' + CAST(@AccountID AS VARCHAR(10)), @InterestEarned, 0.00), (GETDATE(), 'CustomerDeposits', 'Interest credited to Account ' + CAST(@AccountID AS VARCHAR(10)), 0.00, @InterestEarned); COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; -- Handle error here if necessary END CATCH; -- Fetch next row FETCH NEXT FROM AccountCursor INTO @AccountID, @CurrentBalance, @InterestRate; END; -- 4. Clean up Cursor resources CLOSE AccountCursor; DEALLOCATE AccountCursor; END; ``` --- #### 🔒 Example 3: Transfer Money with Conditional Rollback (Transaction + IF) **The Business Requirement:** Move funds safely between a source account and a destination account. To prevent overdrafts and **race conditions** (double-spend attempts), acquire a pessimistic write-lock (`UPDLOCK, ROWLOCK`) on the sender's balance using SQL Server table hints. If funds are insufficient, abort immediately. If successful, debit the source, credit the target, write transaction logs, post ledger entries, and commit. ##### T-SQL Implementation (Stored Procedure): ```sql CREATE PROCEDURE dbo.TransferMoney @FromAccount INT, @ToAccount INT, @Amount DECIMAL(18,2), @ResultStatus VARCHAR(100) OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @vBalance DECIMAL(18,2); BEGIN TRANSACTION; BEGIN TRY -- 1. Acquire pessimistic write-lock using SQL Server table hints SELECT @vBalance = current_balance FROM accounts WITH (UPDLOCK, ROWLOCK) WHERE account_id = @FromAccount; -- 2. Validate funds IF @vBalance < @Amount BEGIN SET @ResultStatus = 'Transfer Rejected: Insufficient funds'; ROLLBACK TRANSACTION; RETURN; END; -- 3. Perform the debit UPDATE accounts SET current_balance = current_balance - @Amount WHERE account_id = @FromAccount; -- 4. Perform the credit UPDATE accounts SET current_balance = current_balance + @Amount WHERE account_id = @ToAccount; -- 5. Log transaction entries INSERT INTO transactions (account_id, amount, transaction_type, description, reference_id) VALUES (@FromAccount, -@Amount, 'transfer', 'Electronic Transfer Out', CAST(@ToAccount AS VARCHAR(20))); INSERT INTO transactions (account_id, amount, transaction_type, description, reference_id) VALUES (@ToAccount, @Amount, 'transfer', 'Electronic Transfer In', CAST(@FromAccount AS VARCHAR(20))); -- 6. General Ledger logging INSERT INTO ledger_entries (entry_date, account_code, description, debit, credit) VALUES (GETDATE(), 'CustomerDeposits', 'Debit account transfer ' + CAST(@FromAccount AS VARCHAR(10)), @Amount, 0.00), (GETDATE(), 'CustomerDeposits', 'Credit account transfer ' + CAST(@ToAccount AS VARCHAR(10)), 0.00, @Amount); COMMIT TRANSACTION; SET @ResultStatus = 'Transfer successful'; END TRY BEGIN CATCH -- Check if active transaction exists before rolling back IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; SET @ResultStatus = 'Transaction Failed: ' + ERROR_MESSAGE(); END CATCH; END; ``` --- #### 🌳 Example 4: Recursive Logic – Find All Subordinates (Manager Hierarchy) **The Business Requirement:** Retrieve a complete hierarchical chart of all employees reporting directly or indirectly to a specific executive manager in SQL Server. ##### T-SQL Recursive CTE Implementation: ```sql -- SQL Server Recursive CTE syntax WITH SubordinateTree AS ( -- 1. Anchor Member: Locate top-tier manager SELECT employee_id, full_name, manager_id, role, 1 AS depth FROM employees WHERE employee_id = 101 UNION ALL -- 2. Recursive Member: Join subordinates to active hierarchy depth SELECT e.employee_id, e.full_name, e.manager_id, e.role, st.depth + 1 FROM employees e INNER JOIN SubordinateTree st ON e.manager_id = st.employee_id WHERE st.depth < 10 -- Safety check to prevent infinite loops ) SELECT depth, employee_id, full_name, role, manager_id FROM SubordinateTree ORDER BY depth, employee_id; ``` --- #### 📊 Example 5: Dynamic SQL & PIVOT – Monthly Deposits Per Branch **The Business Requirement:** Generate a monthly financial report showing total deposits grouped by branch. Since the active months inside a dataset grow over the year, compile the dynamic SQL code utilizing SQL Server's native `PIVOT` statement and execute using `sp_executesql`. ##### T-SQL Dynamic PIVOT Implementation: ```sql CREATE PROCEDURE dbo.GetMonthlyDepositsPivot AS BEGIN SET NOCOUNT ON; DECLARE @ColumnList NVARCHAR(MAX); DECLARE @DynamicSQL NVARCHAR(MAX); -- 1. Aggregate unique month columns dynamically (e.g. [01], [02], [03]...) SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + QUOTENAME(MonthStr) FROM ( SELECT DISTINCT FORMAT(transaction_date, 'MM') AS MonthStr FROM transactions WHERE transaction_date >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1) ) AS MonthQuery; -- 2. Formulate dynamic SQL incorporating SQL Server native PIVOT operator SET @DynamicSQL = N' SELECT BranchName, ' + @ColumnList + N' FROM ( SELECT b.branch_name AS BranchName, FORMAT(t.transaction_date, 'MM') AS MonthStr, t.amount FROM branches b LEFT JOIN accounts a ON b.branch_id = a.branch_id LEFT JOIN transactions t ON a.account_id = t.account_id WHERE t.transaction_type = 'deposit' ) AS SourceData PIVOT ( SUM(amount) FOR MonthStr IN (' + @ColumnList + N') ) AS PivotTable;'; -- 3. Execute the SQL Server dynamic string EXEC sp_executesql @DynamicSQL; END; ``` --- ### 🗺️ 3. How to Develop Logic in SQL – Stepwise Approach When you receive a banking requirement, follow this structured methodology to write safe, clean procedural T-SQL: ```mermaid graph TD A[Understand Business Rule] --> B{Can this be solved with Set-Based SQL?} B -- Yes --> C[Write a single INSERT/UPDATE/DELETE query] B -- No --> D[Define @Variables & Temp Storage] D --> E[Design Procedural Loop or Cursor] E --> F[Incorporate UPDLOCK / ROWLOCK Transaction Hints] F --> G[Wrap in TRY...CATCH Blocks with @@TRANCOUNT checks] ``` #### Step 1: Understand the Business Rule and Constraints Explicitly define conditions, penalty fractions, interest thresholds, grace windows, and fallback limits. #### Step 2: Set-Based vs. Procedural Decision Always check if a single, highly performant set-based transaction (like `UPDATE accounts SET current_balance = ... WHERE status = 'active'`) can solve the requirement. **Set-based SQL is compiled and processed orders of magnitude faster than loops.** Choose procedural loops *only* if you must invoke distinct per-row calculations, call external APIs, or manage dynamic schemas. #### Step 3: Write Procedural Pseudocode Draft the algorithm in plain English comments before typing T-SQL: ```text -- Retrieve savings accounts where balance is less than $500 -- For each matching account: -- Verify if fee was already processed this month -- If not, deduct $10 maintenance charge -- Log fee transaction -- Log general ledger expense ``` #### Step 4: Map to SQL Control Structures * **Variable Storage:** Declare type constraints carefully (use `DECIMAL(15,2)` for money, never float). * **Sequential Logic:** Map conditional pathways using `IF...ELSE` statements. * **Loop Containers:** Use `WHILE` structures over raw cursors to simplify loop cleanup. #### Step 5: Wrap with Exception Handlers and Safety Rollbacks Always verify that any transactional exception automatically triggers a database-wide rollback (`IF @@TRANCOUNT > 0 ROLLBACK`) to prevent partial updates. --- ### 📋 4. Common Banking Algorithms Implemented in SQL Server | Banking Logic Requirement | Preferred T-SQL Technique | Implementation Rationale | | :--- | :--- | :--- | | **Daily Interest Accrual** | Set-based bulk `UPDATE` | Modifies millions of savings accounts in a single pass at midnight. | | **Loan Amortization** | Recursive Common Table Expression (CTE) | Generates multi-row payment schedules mathematically from a single loan row. | | **Early Repayment Penalty** | `IF...ELSE` conditional block | Compares elapsed days to calculate prepayment fees. | | **Fraud Verification (Rapid Transfers)** | Window functions (`LAG`, `LEAD`) | Evaluates current transactions against previous rows within microsecond windows. | | **Credit limit Enforcement** | `INSTEAD OF INSERT` Database Trigger | Blocks over-drafts at the database level before transactions can be committed. | | **Multi-Tier Approvals** | Hierarchical Recursive CTE | Traces reporting chains upward to locate managers with appropriate approval limits. | --- ### 💡 5. Pro Optimization Rules for Procedural SQL Server 1. **Avoid the RBAR Trap (Row-By-Row-Agony):** Beginners often write a loop to update millions of rows individually. Instead, write a single bulk `UPDATE` query with a `WHERE` filter. Set-based queries are parallelized by the query optimizer; loops are single-threaded. 2. **Use Explicit Field Lists on INSERT:** Always write `INSERT INTO accounts (account_id, current_balance) VALUES (...)` instead of `INSERT INTO accounts VALUES (...)`. If a database administrator adds or alters a column in the future, shorthand queries will break. 3. **Perform Batch Commits:** If processing massive amounts of data, commit transactions in batches (e.g., every 10,000 rows) to prevent the transaction log volume from running out of disk space. 4. **Acquire Early locks:** When performing multi-row accounting updates, lock the primary table rows (`WITH (UPDLOCK, ROWLOCK)`) immediately to prevent deadlocks and race conditions. --- ### 🧠 6. Solved Practice Challenge #### The Problem Statement: Write an optimized stored procedure to identify savings accounts whose average balance over the last 3 months fell below the required $500 minimum. For each matching account, deduct a $10 maintenance fee and log the fee as an account transaction. * **Rule 1:** Prevent double charges by ensuring no account is billed more than once per calendar month. * **Rule 2:** The deduction must be recorded in both the customer account transactions ledger and the double-entry accounting ledger (`ledger_entries`). #### Step-by-Step T-SQL Solution: Let's build this logic using a set-based query first to avoid expensive loops, with an anti-join (`NOT EXISTS`) guard to prevent double-charging: ```sql -- Set-Based Procedural Solution (Microsoft SQL Server / T-SQL) CREATE PROCEDURE dbo.ChargeMaintenanceFees AS BEGIN SET NOCOUNT ON; DECLARE @FeeAmount DECIMAL(5,2) = 10.00; DECLARE @MinReqBalance DECIMAL(15,2) = 500.00; DECLARE @CurrentMonthStart DATE = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1); BEGIN TRANSACTION; BEGIN TRY -- 1. Insert into transactions for active savings accounts below minimum balance -- who have NOT already been charged a maintenance fee this month INSERT INTO transactions (account_id, amount, transaction_type, description, transaction_date) SELECT a.account_id, -@FeeAmount, 'fee', 'Monthly Minimum Balance Fee - ' + FORMAT(GETDATE(), 'MMMM yyyy'), GETDATE() FROM accounts a INNER JOIN account_types at ON a.type_id = at.type_id WHERE a.status = 'active' AND at.type_name = 'savings' AND a.current_balance = CAST(@CurrentMonthStart AS DATETIME) ); -- 2. Post corresponding entries to the double-entry general ledger -- Debit customer deposits (reducing bank liabilities), credit ledger income INSERT INTO ledger_entries (entry_date, account_code, description, debit, credit) SELECT GETDATE(), 'CustomerDeposits', 'Maintenance charge deducted for Account ' + CAST(a.account_id AS VARCHAR(10)), @FeeAmount, 0.00 FROM accounts a INNER JOIN account_types at ON a.type_id = at.type_id WHERE a.status = 'active' AND at.type_name = 'savings' AND a.current_balance = @CurrentMonthStart ); INSERT INTO ledger_entries (entry_date, account_code, description, debit, credit) SELECT GETDATE(), 'FeeRevenue', 'Maintenance fee recognized for Account ' + CAST(a.account_id AS VARCHAR(10)), 0.00, @FeeAmount FROM accounts a INNER JOIN account_types at ON a.type_id = at.type_id WHERE a.status = 'active' AND at.type_name = 'savings' AND a.current_balance = @CurrentMonthStart ); -- 3. Update the balances in the main accounts table UPDATE accounts SET current_balance = current_balance - @FeeAmount WHERE account_id IN ( SELECT a.account_id FROM accounts a INNER JOIN account_types at ON a.type_id = at.type_id WHERE a.status = 'active' AND at.type_name = 'savings' AND a.current_balance = CAST(@CurrentMonthStart AS DATETIME) ) ); COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; -- Re-throw the captured error to the calling client THROW; END CATCH; END; ``` This solution uses **set-based operations** and **existential checks** inside a robust transaction boundary to implement complex corporate logic efficiently.