41. API Integration & Backend Patterns

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• API design patterns
• Pagination queries
• Filtering and sorting
• Caching strategies
• Data transformation
• Error handling
• Response formatting
• Rate limiting

APIs need optimized queries. This article teaches backend patterns.

Pattern 1: Paginated Listing

Get students with pagination:

CREATE PROCEDURE sp_GetStudents
    @PageNumber INT = 1,
    @PageSize INT = 20,
    @ClassName NVARCHAR(10) = NULL,
    @SearchName NVARCHAR(100) = NULL,
    @OrderBy NVARCHAR(50) = 'Name'
AS
BEGIN
    -- Validate
    IF @PageNumber < 1 SET @PageNumber = 1;
    IF @PageSize > 100 SET @PageSize = 100;  -- Max 100
    
    DECLARE @Skip INT = (@PageNumber - 1) * @PageSize;
    
    -- Get total count
    DECLARE @TotalCount INT;
    SELECT @TotalCount = COUNT(*)
    FROM Student
    WHERE (@ClassName IS NULL OR ClassName = @ClassName)
    AND (@SearchName IS NULL OR Name LIKE '%' + @SearchName + '%');
    
    -- Get page data
    SELECT 
        StudentId,
        Name,
        ClassName,
        RollNumber,
        Status,
        @TotalCount AS TotalCount,
        @PageNumber AS PageNumber,
        @PageSize AS PageSize,
        CEILING(CAST(@TotalCount AS FLOAT) / @PageSize) AS TotalPages
    FROM Student
    WHERE (@ClassName IS NULL OR ClassName = @ClassName)
    AND (@SearchName IS NULL OR Name LIKE '%' + @SearchName + '%')
    ORDER BY 
        CASE WHEN @OrderBy = 'Name' THEN Name END,
        CASE WHEN @OrderBy = 'ClassName' THEN ClassName END
    OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY;
END;

-- Usage from API:
-- GET /api/students?page=1&pageSize=20&className=10-A&search=ravi&orderBy=Name

API returns: Data + total count + pages.

Pattern 2: Efficient Filtering

Fast filtered queries:

CREATE PROCEDURE sp_FilterExamResults
    @MinMarks DECIMAL(5,2) = 0,
    @MaxMarks DECIMAL(5,2) = 100,
    @ClassName NVARCHAR(10) = NULL,
    @Subject NVARCHAR(100) = NULL
AS
BEGIN
    SELECT 
        s.Name AS Student,
        s.ClassName,
        sub.Name AS Subject,
        r.MarksObtained
    FROM Student s
    INNER JOIN ExamResult r ON s.StudentId = r.StudentId
    INNER JOIN Exam e ON r.ExamId = e.ExamId
    INNER JOIN Subject sub ON e.SubjectId = sub.SubjectId
    WHERE r.MarksObtained BETWEEN @MinMarks AND @MaxMarks
    AND (@ClassName IS NULL OR s.ClassName = @ClassName)
    AND (@Subject IS NULL OR sub.Name = @Subject)
    ORDER BY r.MarksObtained DESC;
END;

-- Indexes for filters:
CREATE INDEX IX_ExamResult_Marks ON ExamResult(MarksObtained);
CREATE INDEX IX_Student_ClassName ON Student(ClassName);
CREATE INDEX IX_Subject_Name ON Subject(Name);

Filters with indexes = fast.

Pattern 3: Response Formatting

Format for API response:

CREATE PROCEDURE sp_GetStudentDetail
    @StudentId INT
AS
BEGIN
    SELECT 
        StudentId,
        Name,
        ClassName,
        RollNumber,
        (
            SELECT COUNT(*) FROM ExamResult
            WHERE StudentId = @StudentId
        ) AS ExamCount,
        (
            SELECT AVG(MarksObtained) FROM ExamResult
            WHERE StudentId = @StudentId
        ) AS AvgMarks,
        (
            SELECT SUM(PaidAmount) FROM FeePayment fp
            INNER JOIN FeeAccount f ON fp.FeeAccountId = f.FeeAccountId
            WHERE f.StudentId = @StudentId
        ) AS TotalPaid,
        (
            SELECT f.TotalFees - f.PaidAmount FROM FeeAccount f
            WHERE f.StudentId = @StudentId
            AND f.AcademicYear = CAST(YEAR(GETDATE()) AS VARCHAR(4))
        ) AS FeeDue
    FROM Student
    WHERE StudentId = @StudentId
    FOR JSON AUTO;  -- Return as JSON for API
END;

-- Result JSON:
-- { "StudentId": 1, "Name": "Ravi", "ExamCount": 5, "AvgMarks": 85.5, ... }

FOR JSON: Direct API response format.

Pattern 4: Caching Query Results

Cache expensive queries:

-- Materialized view (auto-updated)
CREATE VIEW vw_ClassStats WITH SCHEMABINDING AS
SELECT 
    ClassName,
    COUNT(DISTINCT StudentId) AS StudentCount,
    AVG(CAST(mo.AvgMarks AS DECIMAL(10,2))) AS AvgMarks,
    MAX(mo.AvgMarks) AS HighestAvg,
    GETDATE() AS UpdatedAt
FROM dbo.Student s
CROSS APPLY (
    SELECT AVG(MarksObtained) AS AvgMarks
    FROM dbo.ExamResult
    WHERE StudentId = s.StudentId
) mo
GROUP BY ClassName;

-- Index to materialize
CREATE UNIQUE CLUSTERED INDEX ix_ClassStats ON vw_ClassStats(ClassName);

-- API query (instant):
SELECT * FROM vw_ClassStats;

Materialized view caches. Index updates on demand.

Pattern 5: Rate Limiting

Track API usage:

CREATE TABLE ApiRateLimit (
    LimitId INT PRIMARY KEY IDENTITY(1,1),
    ApiKey NVARCHAR(100),
    Endpoint NVARCHAR(200),
    RequestCount INT,
    ResetTime DATETIME,
    MaxRequests INT DEFAULT 100
);

-- Check limit
CREATE PROCEDURE sp_CheckRateLimit
    @ApiKey NVARCHAR(100),
    @Endpoint NVARCHAR(200),
    @IsAllowed BIT OUTPUT,
    @RemainingRequests INT OUTPUT
AS
BEGIN
    DECLARE @MaxRequests INT = 100;
    DECLARE @TimeWindow INT = 60;  -- Seconds
    
    -- Check/create limit record
    IF NOT EXISTS (SELECT 1 FROM ApiRateLimit WHERE ApiKey = @ApiKey AND Endpoint = @Endpoint)
    BEGIN
        INSERT INTO ApiRateLimit (ApiKey, Endpoint, RequestCount, ResetTime, MaxRequests)
        VALUES (@ApiKey, @Endpoint, 1, DATEADD(SECOND, @TimeWindow, GETDATE()), @MaxRequests);
        SET @IsAllowed = 1;
        SET @RemainingRequests = @MaxRequests - 1;
    END
    ELSE
    BEGIN
        -- Check if window expired
        DECLARE @ResetTime DATETIME;
        SELECT @ResetTime = ResetTime FROM ApiRateLimit WHERE ApiKey = @ApiKey AND Endpoint = @Endpoint;
        
        IF GETDATE() > @ResetTime
        BEGIN
            -- Reset window
            UPDATE ApiRateLimit
            SET RequestCount = 1, ResetTime = DATEADD(SECOND, @TimeWindow, GETDATE())
            WHERE ApiKey = @ApiKey AND Endpoint = @Endpoint;
            SET @IsAllowed = 1;
            SET @RemainingRequests = @MaxRequests - 1;
        END
        ELSE IF (SELECT RequestCount FROM ApiRateLimit WHERE ApiKey = @ApiKey AND Endpoint = @Endpoint) < @MaxRequests
        BEGIN
            -- Increment
            UPDATE ApiRateLimit
            SET RequestCount = RequestCount + 1
            WHERE ApiKey = @ApiKey AND Endpoint = @Endpoint;
            
            SELECT @RemainingRequests = @MaxRequests - RequestCount
            FROM ApiRateLimit WHERE ApiKey = @ApiKey AND Endpoint = @Endpoint;
            SET @IsAllowed = 1;
        END
        ELSE
        BEGIN
            -- Limit exceeded
            SET @IsAllowed = 0;
            SET @RemainingRequests = 0;
        END
    END
END;

-- Use in API middleware:
EXEC sp_CheckRateLimit @ApiKey, @Endpoint, @IsAllowed OUTPUT, @Remaining OUTPUT;
IF @IsAllowed = 0
    RETURN 429;  -- Too Many Requests

Rate limit: Track requests per time window.

---

🎯 Q1: How do I design paginated API?

Query: OFFSET/FETCH + total count + pages.

OFFSET (@Page - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;
SELECT COUNT(*) AS Total;

Return: Data + total + current page + total pages.

🎯 Q2: How do I optimize filtering?

Add indexes on filter columns:

CREATE INDEX IX_Student_ClassName ON Student(ClassName);
CREATE INDEX IX_ExamResult_Marks ON ExamResult(MarksObtained);

Composite index if multiple filters:

CREATE INDEX IX_Result_StudentMarks ON ExamResult(StudentId, MarksObtained);

🎯 Q3: Should I cache query results?

Cache if:

• Expensive query (multiple JOINs)
• Frequent reads
• Data changes infrequently

Use: Materialized view or application cache.

🎯 Q4: How do I return JSON from SQL?

SELECT * FROM Student WHERE StudentId = @Id
FOR JSON AUTO;
-- Returns JSON directly, ready for API response

FOR JSON Auto: JSON structure matches table.

🎯 Q5: How do I implement rate limiting?

Track requests per API key + endpoint. Reset every N seconds. Reject if count exceeded.

Prevents abuse, ensures fair usage.

🎯 Q6: When is OFFSET/FETCH better than TOP?

OFFSET/FETCH: True pagination (skip rows). TOP: Get first N only.

Use OFFSET for APIs (variable page). Use TOP for limits (top 10).

---

🤖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 API integration and backend patterns in SQL Server. Try these prompts:

• "Show paginated API endpoint (page, pageSize, total)"
• "How do I optimize filtering with indexes?"
• "What's the best way to return JSON from SQL?"
• "How do I implement rate limiting for API?"
• "Quiz me: 5 questions about API patterns, pagination, caching"

💡 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

42. Compliance & Audit Scenarios ->