38. Complex Business Logic -- Multi-Step Processes

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• Multi-step workflows in transactions
• State management (status tracking)
• Conditional branching logic
• Error propagation and recovery
• Notification patterns
• Audit trails for compliance
• Saga pattern (distributed workflows)
• Best practices for complex logic

Complex processes need careful orchestration. This article teaches multi-step logic.

Scenario 1: Semester Enrollment Workflow

Complete enrollment with dependencies:

CREATE PROCEDURE sp_CompleteSemesterEnrollment
    @StudentId INT,
    @NewClassName NVARCHAR(10),
    @AcademicYear NVARCHAR(9),
    @IsSuccess BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Step 1: Validate student exists and active
        IF NOT EXISTS (SELECT 1 FROM Student WHERE StudentId = @StudentId AND Status = 'Active')
        BEGIN
            THROW 50001, 'Student not active or not found', 1;
        END;
        
        -- Step 2: Validate class exists
        IF NOT EXISTS (SELECT 1 FROM Class WHERE ClassName = @NewClassName)
        BEGIN
            THROW 50002, 'Class not found', 1;
        END;
        
        -- Step 3: Update student class
        UPDATE Student SET ClassName = @NewClassName WHERE StudentId = @StudentId;
        
        -- Step 4: Create fee account for new academic year
        INSERT INTO FeeAccount (StudentId, AcademicYear, TotalFees, Status)
        SELECT @StudentId, @AcademicYear, c.FeesAmount, 'Pending'
        FROM Class c WHERE ClassName = @NewClassName;
        
        -- Step 5: Initialize attendance records (create for each subject)
        INSERT INTO Attendance (StudentId, SubjectId, Date, IsPresent)
        SELECT @StudentId, SubjectId, GETDATE(), 0
        FROM Subject WHERE ClassName = @NewClassName;
        
        -- Step 6: Log enrollment
        INSERT INTO AuditLog (Action, EntityName, EntityId, NewValues, ChangedBy, ChangedAt)
        VALUES (
            'SemesterEnroll',
            'Student',
            @StudentId,
            CONCAT('Class=', @NewClassName, ', Year=', @AcademicYear),
            SYSTEM_USER,
            GETDATE()
        );
        
        -- Step 7: Commit all changes
        COMMIT TRANSACTION;
        SET @IsSuccess = 1;
        SET @ErrorMessage = 'Enrollment completed successfully';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @IsSuccess = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

All-or-nothing: Class + Fees + Attendance + Audit.

Scenario 2: Fee Payment with Reconciliation

Multi-step payment processing:

CREATE PROCEDURE sp_ProcessFeePayment
    @StudentId INT,
    @Amount DECIMAL(10,2),
    @PaymentMode NVARCHAR(20),
    @TransactionId NVARCHAR(50),
    @PaymentId INT OUTPUT,
    @IsSuccess BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @FeeAccountId INT;
        DECLARE @DueAmount DECIMAL(10,2);
        
        -- Step 1: Get current fee account
        SELECT TOP 1 @FeeAccountId = FeeAccountId, @DueAmount = (TotalFees - PaidAmount)
        FROM FeeAccount
        WHERE StudentId = @StudentId AND Status != 'Paid'
        ORDER BY AcademicYear DESC;
        
        IF @FeeAccountId IS NULL
        BEGIN
            THROW 50001, 'No pending fee account found', 1;
        END;
        
        -- Step 2: Validate amount
        IF @Amount > @DueAmount
        BEGIN
            THROW 50002, CONCAT('Amount exceeds due (', @DueAmount, ')'), 1;
        END;
        
        -- Step 3: Record payment
        INSERT INTO FeePayment (FeeAccountId, Amount, PaidOn, PaymentMode, TransactionId, CollectedBy)
        VALUES (@FeeAccountId, @Amount, GETDATE(), @PaymentMode, @TransactionId, SYSTEM_USER);
        
        SET @PaymentId = SCOPE_IDENTITY();
        
        -- Step 4: Update fee account
        DECLARE @NewPaidAmount DECIMAL(10,2);
        SELECT @NewPaidAmount = PaidAmount + @Amount FROM FeeAccount WHERE FeeAccountId = @FeeAccountId;
        
        UPDATE FeeAccount
        SET PaidAmount = @NewPaidAmount,
            Status = CASE WHEN @NewPaidAmount >= TotalFees THEN 'Paid' ELSE 'Partial' END
        WHERE FeeAccountId = @FeeAccountId;
        
        -- Step 5: Log transaction
        INSERT INTO AuditLog (Action, EntityName, EntityId, NewValues, ChangedBy, ChangedAt)
        VALUES (
            'FeePayment',
            'FeePayment',
            @PaymentId,
            CONCAT('Amount=', @Amount, ', Mode=', @PaymentMode),
            SYSTEM_USER,
            GETDATE()
        );
        
        COMMIT TRANSACTION;
        SET @IsSuccess = 1;
        SET @ErrorMessage = 'Payment processed successfully';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @IsSuccess = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

Payment: Validate -> Record -> Update account -> Log.

Scenario 3: State Management (Academic Workflow)

Track student academic status:

CREATE TABLE StudentAcademicStatus (
    StatusId INT PRIMARY KEY IDENTITY(1,1),
    StudentId INT,
    Status NVARCHAR(20),  -- Enrolled, InProgress, Completed, Failed, Withdrawn
    ChangeReason NVARCHAR(200),
    ChangedAt DATETIME DEFAULT GETDATE(),
    ChangedBy NVARCHAR(100),
    FOREIGN KEY (StudentId) REFERENCES Student(StudentId)
);

-- Procedure to change status with validation
CREATE PROCEDURE sp_UpdateAcademicStatus
    @StudentId INT,
    @NewStatus NVARCHAR(20),
    @Reason NVARCHAR(200),
    @IsSuccess BIT OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    
    BEGIN TRY
        DECLARE @CurrentStatus NVARCHAR(20);
        
        -- Get current status
        SELECT TOP 1 @CurrentStatus = Status
        FROM StudentAcademicStatus
        WHERE StudentId = @StudentId
        ORDER BY StatusId DESC;
        
        -- Validate state transition
        IF @CurrentStatus = 'Completed' AND @NewStatus != 'Completed'
        BEGIN
            THROW 50001, 'Cannot change status from Completed', 1;
        END;
        
        IF @CurrentStatus = 'Withdrawn' AND @NewStatus != 'Withdrawn'
        BEGIN
            THROW 50002, 'Cannot change status from Withdrawn', 1;
        END;
        
        -- Update
        INSERT INTO StudentAcademicStatus (StudentId, Status, ChangeReason, ChangedBy)
        VALUES (@StudentId, @NewStatus, @Reason, SYSTEM_USER);
        
        SET @IsSuccess = 1;
    END TRY
    BEGIN CATCH
        SET @IsSuccess = 0;
        THROW;
    END CATCH
END;

State machine: Validate transitions before changing status.

Scenario 4: Report Generation Workflow

Multi-step report creation:

CREATE PROCEDURE sp_GenerateProgressReport
    @StudentId INT,
    @ReportDate DATE
AS
BEGIN
    BEGIN TRY
        -- Step 1: Validate student
        DECLARE @StudentName NVARCHAR(100);
        SELECT @StudentName = Name FROM Student WHERE StudentId = @StudentId;
        
        IF @StudentName IS NULL
        BEGIN
            THROW 50001, 'Student not found', 1;
        END;
        
        -- Step 2: Create temp report table
        CREATE TABLE #ProgressReport (
            StudentName NVARCHAR(100),
            ClassName NVARCHAR(10),
            Subject NVARCHAR(100),
            AvgMarks DECIMAL(5,2),
            Grade NVARCHAR(2),
            Attendance DECIMAL(5,2),
            Status NVARCHAR(20)
        );
        
        -- Step 3: Populate with academic data
        INSERT INTO #ProgressReport
        SELECT 
            s.Name,
            s.ClassName,
            sub.Name,
            AVG(r.MarksObtained),
            CASE WHEN AVG(r.MarksObtained) >= 80 THEN 'A'
                 WHEN AVG(r.MarksObtained) >= 70 THEN 'B'
                 ELSE 'C' END,
            ROUND(
                SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) * 100.0 / 
                COUNT(a.AttendanceId), 2
            ),
            CASE WHEN AVG(r.MarksObtained) < 60 THEN 'At Risk'
                 ELSE 'Good' END
        FROM Student s
        LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
        LEFT JOIN Exam e ON r.ExamId = e.ExamId
        LEFT JOIN Subject sub ON e.SubjectId = sub.SubjectId
        LEFT JOIN Attendance a ON s.StudentId = a.StudentId
        WHERE s.StudentId = @StudentId
        GROUP BY s.Name, s.ClassName, sub.Name;
        
        -- Step 4: Return formatted report
        SELECT * FROM #ProgressReport;
        
        -- Step 5: Log report generation
        INSERT INTO AuditLog (Action, EntityName, EntityId, ChangedBy, ChangedAt)
        VALUES ('GenerateReport', 'Student', @StudentId, SYSTEM_USER, GETDATE());
        
        -- Step 6: Cleanup
        DROP TABLE #ProgressReport;
    END TRY
    BEGIN CATCH
        DROP TABLE IF EXISTS #ProgressReport;
        THROW;
    END CATCH
END;

Report: Validate -> Gather -> Format -> Log -> Cleanup.

---

🎯 Q1: How do I handle multi-step workflows?

Use transaction + stored procedure:

BEGIN TRANSACTION;
BEGIN TRY
    Step 1: Validate
    Step 2: Update Table1
    Step 3: Update Table2
    Step 4: Log
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
END CATCH

All steps succeed or all rollback.

🎯 Q2: How do I manage state transitions?

Track status changes + validate transitions:

Current: Enrolled
Valid next: InProgress, Withdrawn
Invalid next: Completed (only from InProgress)

Validate before UPDATE.

State machine prevents invalid transitions.

🎯 Q3: How do I ensure payment reconciliation?

Check: amount <= due, update account, record payment, log.

Atomicity: All or rollback.

🎯 Q4: Should I use temp tables in procedures?

Yes, for intermediate results:

CREATE TABLE #TempData (...);
INSERT INTO #TempData ... FROM ...;
SELECT * FROM #TempData;
DROP TABLE #TempData;

Cleanup: DROP TABLE in CATCH too.

🎯 Q5: How do I handle dependent operations?

Order matters:

1. Validate all prerequisites
2. Execute in dependency order
3. Log after success
4. Rollback on any failure

Transaction ensures atomicity.

🎯 Q6: How do I log complex operations?

Log summary + key data:

INSERT INTO AuditLog (Action, EntityId, NewValues)
VALUES ('ComplexOp', @Id, 'Step1=OK, Step2=OK, Result=Success');

Track progress for debugging.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Complex business logic and multi-step workflows. Try these prompts:

• "Show semester enrollment with multiple steps (class, fees, attendance)"
• "How do I process payment with reconciliation?"
• "Explain state machine: validate transitions before update"
• "How do I generate multi-step reports with temp tables?"
• "Quiz me: 5 questions about workflows, state, atomicity"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

39. Troubleshooting & Debugging -- Finding and Fixing Issues ->