Enterprise API Integration Guide: SQL Server 2025 + JWT + Complex JSON Handling
1. The New Era of Database Connectivity
Welcome to FreeLearning365.com, where we explore cutting-edge technology solutions! In today's data-driven world, databases are no longer isolated silos but dynamic components of interconnected systems. SQL Server 2025 introduces groundbreaking capabilities that transform how we integrate with external APIs, handle complex data structures, and secure enterprise data flows.
The Challenge: Modern applications demand real-time data integration with various REST APIs, often requiring JWT authentication, handling complex nested JSON responses, processing bulk data efficiently, and maintaining enterprise-grade security—all while ensuring performance and reliability.
The Solution: SQL Server 2025's enhanced REST API capabilities, combined with strategic implementation patterns, provide a comprehensive framework for building robust, secure, and scalable API integrations directly from your database layer.
2. What You'll Learn
By the end of this comprehensive guide, you'll master:
✅ JWT Authentication Management - Secure token acquisition, refresh, and storage with encryption
✅ Complex JSON Processing - Handling nested JSON structures with SQL Server's native JSON functions
✅ Bulk Data Operations - Efficiently processing thousands of API records with pagination and chunking
✅ Enterprise Security - Implementing encryption, rate limiting, and comprehensive audit logging
✅ Error Handling & Resilience - Building fault-tolerant systems with retry logic and monitoring
✅ Real API Integration - Working with free APIs like JSONPlaceholder and ReqRes.in
✅ Performance Optimization - Best practices for high-volume API integrations
✅ Monitoring & Maintenance - Creating dashboards and automated cleanup procedures
Useful Resources:
1. Free Open-Source API Endpoints for Testing
A. Available Free APIs with JWT Support:
JSONPlaceholder - Fake REST API (No auth required)
ReqRes.in - Supports JWT authentication
GoRest API - Free tier with JWT
JSON Server - Local mock API with JWT
2. Complete Setup with Real Examples
A. Database Schema with Enhanced Security
-- Enable Always Encrypted for sensitive data CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword123!'; CREATE CERTIFICATE ApiEncryptionCert WITH SUBJECT = 'API Secrets Encryption'; CREATE SYMMETRIC KEY ApiSymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ApiEncryptionCert; -- Main configuration table with encryption CREATE TABLE dbo.ApiConfig ( ConfigId INT IDENTITY(1,1) PRIMARY KEY, ApiName NVARCHAR(100) NOT NULL UNIQUE, BaseUrl NVARCHAR(500) NOT NULL, AuthEndpoint NVARCHAR(500), ClientId VARBINARY(MAX), -- Encrypted ClientSecret VARBINARY(MAX), -- Encrypted TokenEndpoint NVARCHAR(500), GrantType NVARCHAR(50) DEFAULT 'password', Scope NVARCHAR(500), TokenExpiryMinutes INT DEFAULT 55, MaxRetries INT DEFAULT 3, TimeoutSeconds INT DEFAULT 30, RateLimitPerMinute INT DEFAULT 60, IsActive BIT DEFAULT 1, CreatedBy SYSNAME DEFAULT SUSER_SNAME(), CreatedDate DATETIME DEFAULT GETDATE(), UpdatedBy SYSNAME, UpdatedDate DATETIME, INDEX IX_ApiName (ApiName), INDEX IX_IsActive (IsActive) ); -- Encrypted token storage CREATE TABLE dbo.ApiTokenStore ( TokenId INT IDENTITY(1,1) PRIMARY KEY, ConfigId INT FOREIGN KEY REFERENCES dbo.ApiConfig(ConfigId), AccessToken VARBINARY(MAX) NOT NULL, -- Encrypted TokenType NVARCHAR(50), ExpiresIn INT, RefreshToken VARBINARY(MAX), -- Encrypted Scope NVARCHAR(500), AcquiredAt DATETIME DEFAULT GETDATE(), ExpiresAt DATETIME, IsValid BIT DEFAULT 1, INDEX IX_ConfigId_Valid (ConfigId, IsValid), INDEX IX_ExpiresAt (ExpiresAt) ); -- Comprehensive error logging CREATE TABLE dbo.ApiErrorLog ( ErrorId BIGINT IDENTITY(1,1) PRIMARY KEY, ConfigId INT, ApiName NVARCHAR(100), Endpoint NVARCHAR(500), HttpMethod NVARCHAR(10), ErrorCode NVARCHAR(50), ErrorMessage NVARCHAR(MAX), StatusCode INT, ResponseBody NVARCHAR(MAX), RequestPayload NVARCHAR(MAX), RequestHeaders NVARCHAR(MAX), ResponseHeaders NVARCHAR(MAX), StackTrace NVARCHAR(MAX), RetryCount INT, BatchId UNIQUEIDENTIFIER, CorrelationId UNIQUEIDENTIFIER DEFAULT NEWID(), ErrorTime DATETIME DEFAULT GETDATE(), Resolved BIT DEFAULT 0, ResolvedBy SYSNAME, ResolvedDate DATETIME, INDEX IX_ErrorTime (ErrorTime), INDEX IX_Resolved (Resolved), INDEX IX_CorrelationId (CorrelationId), INDEX IX_BatchId_Error (BatchId) ); -- Audit log for all API calls CREATE TABLE dbo.ApiAuditLog ( AuditId BIGINT IDENTITY(1,1) PRIMARY KEY, ConfigId INT, ApiName NVARCHAR(100), Endpoint NVARCHAR(500), HttpMethod NVARCHAR(10), StatusCode INT, RequestSizeBytes INT, ResponseSizeBytes INT, DurationMs INT, CorrelationId UNIQUEIDENTIFIER DEFAULT NEWID(), CalledBy SYSNAME DEFAULT SUSER_SNAME(), CalledAt DATETIME DEFAULT GETDATE(), Success BIT, INDEX IX_CalledAt (CalledAt), INDEX IX_CorrelationId_Audit (CorrelationId), INDEX IX_ApiName_Method (ApiName, HttpMethod) ); -- Rate limiting control CREATE TABLE dbo.ApiRateLimit ( RateLimitId INT IDENTITY(1,1) PRIMARY KEY, ConfigId INT FOREIGN KEY REFERENCES dbo.ApiConfig(ConfigId), EndpointPattern NVARCHAR(500), MaxRequestsPerMinute INT DEFAULT 60, CurrentRequestCount INT DEFAULT 0, LastResetTime DATETIME DEFAULT GETDATE(), IsActive BIT DEFAULT 1 ); -- Response staging with compression CREATE TABLE dbo.ApiResponseStage ( StageId BIGINT IDENTITY(1,1) PRIMARY KEY, BatchId UNIQUEIDENTIFIER DEFAULT NEWID(), ConfigId INT, Endpoint NVARCHAR(500), ResponseJson NVARCHAR(MAX), ResponseHeaders NVARCHAR(MAX), StatusCode INT, IsCompressed BIT DEFAULT 0, IsProcessed BIT DEFAULT 0, ProcessedDate DATETIME, CreatedDate DATETIME DEFAULT GETDATE(), INDEX IX_BatchId (BatchId), INDEX IX_IsProcessed (IsProcessed), INDEX IX_CreatedDate (CreatedDate) ) WITH (DATA_COMPRESSION = PAGE); -- Example target table for complex nested JSON CREATE TABLE dbo.UsersData ( UserId INT PRIMARY KEY, Email NVARCHAR(200), FirstName NVARCHAR(100), LastName NVARCHAR(100), Avatar NVARCHAR(500), CompanyName NVARCHAR(200), CompanyCatchPhrase NVARCHAR(500), CompanyBs NVARCHAR(500), AddressStreet NVARCHAR(200), AddressSuite NVARCHAR(100), AddressCity NVARCHAR(100), AddressZipCode NVARCHAR(20), AddressGeoLat DECIMAL(10,6), AddressGeoLng DECIMAL(10,6), Phone NVARCHAR(50), Website NVARCHAR(200), Posts NVARCHAR(MAX), -- Nested JSON array Todos NVARCHAR(MAX), -- Nested JSON array Albums NVARCHAR(MAX), -- Nested JSON array RawResponse NVARCHAR(MAX), LastSynced DATETIME DEFAULT GETDATE(), CreatedDate DATETIME DEFAULT GETDATE(), INDEX IX_Email (Email), INDEX IX_LastName (LastName) );
3. Real API Examples Setup
A. Insert Real Free API Configurations
-- Open symmetric key for encryption OPEN SYMMETRIC KEY ApiSymmetricKey DECRYPTION BY CERTIFICATE ApiEncryptionCert; -- 1. ReqRes.in API (Supports JWT) INSERT INTO dbo.ApiConfig ( ApiName, BaseUrl, AuthEndpoint, ClientId, ClientSecret, TokenEndpoint, GrantType, Scope, TokenExpiryMinutes, MaxRetries, TimeoutSeconds, RateLimitPerMinute ) VALUES ( 'ReqResAPI', 'https://reqres.in/api/', '/login', ENCRYPTBYKEY(KEY_GUID('ApiSymmetricKey'), 'eve.holt@reqres.in'), ENCRYPTBYKEY(KEY_GUID('ApiSymmetricKey'), 'cityslicka'), '/login', 'password', 'read write', 30, 3, 30, 10 -- Limited to 10 req/min (free API) ); -- 2. JSONPlaceholder API (No auth - for testing) INSERT INTO dbo.ApiConfig ( ApiName, BaseUrl, TokenExpiryMinutes, MaxRetries, TimeoutSeconds, RateLimitPerMinute ) VALUES ( 'JSONPlaceholder', 'https://jsonplaceholder.typicode.com/', NULL, 3, 30, 100 ); -- 3. GoRest API (Free tier with JWT) INSERT INTO dbo.ApiConfig ( ApiName, BaseUrl, AuthEndpoint, ClientId, ClientSecret, TokenEndpoint, GrantType, Scope, TokenExpiryMinutes ) VALUES ( 'GoRestAPI', 'https://gorest.co.in/public/v2/', '/oauth/token', ENCRYPTBYKEY(KEY_GUID('ApiSymmetricKey'), 'your_client_id'), ENCRYPTBYKEY(KEY_GUID('ApiSymmetricKey'), 'your_client_secret'), '/oauth/token', 'client_credentials', 'public', 3600 ); CLOSE SYMMETRIC KEY ApiSymmetricKey;
4. Enhanced JWT Token Management with Security
CREATE OR ALTER PROCEDURE dbo.GetSecureApiToken @ConfigId INT, @ForceRefresh BIT = 0, @AccessToken NVARCHAR(MAX) OUTPUT, @TokenType NVARCHAR(50) OUTPUT, @CorrelationId UNIQUEIDENTIFIER OUTPUT, @ErrorMessage NVARCHAR(MAX) OUTPUT AS BEGIN SET NOCOUNT ON; SET @CorrelationId = NEWID(); DECLARE @StartTime DATETIME = GETDATE(); DECLARE @RetryCount INT = 0; DECLARE @MaxRetries INT; DECLARE @Success BIT = 0; DECLARE @TokenResponse NVARCHAR(MAX); DECLARE @StatusCode INT; DECLARE @ResponseHeaders NVARCHAR(MAX); DECLARE @ApiName NVARCHAR(100); DECLARE @BaseUrl NVARCHAR(500); DECLARE @ClientId NVARCHAR(500); DECLARE @ClientSecret NVARCHAR(MAX); DECLARE @TokenEndpoint NVARCHAR(500); DECLARE @GrantType NVARCHAR(50); DECLARE @Scope NVARCHAR(500); BEGIN TRY -- Get API configuration SELECT @ApiName = ApiName, @BaseUrl = BaseUrl, @TokenEndpoint = TokenEndpoint, @GrantType = GrantType, @Scope = Scope, @MaxRetries = MaxRetries FROM dbo.ApiConfig WHERE ConfigId = @ConfigId AND IsActive = 1; IF @ApiName IS NULL BEGIN SET @ErrorMessage = 'API configuration not found or inactive'; THROW 50001, @ErrorMessage, 1; END -- Check rate limiting IF dbo.CheckRateLimit(@ConfigId, @TokenEndpoint) = 0 BEGIN SET @ErrorMessage = 'Rate limit exceeded for token endpoint'; THROW 50002, @ErrorMessage, 1; END -- Decrypt credentials OPEN SYMMETRIC KEY ApiSymmetricKey DECRYPTION BY CERTIFICATE ApiEncryptionCert; SELECT @ClientId = CONVERT(NVARCHAR(500), DECRYPTBYKEY(ClientId)), @ClientSecret = CONVERT(NVARCHAR(MAX), DECRYPTBYKEY(ClientSecret)) FROM dbo.ApiConfig WHERE ConfigId = @ConfigId; CLOSE SYMMETRIC KEY ApiSymmetricKey; -- Check for valid existing token IF @ForceRefresh = 0 BEGIN OPEN SYMMETRIC KEY ApiSymmetricKey DECRYPTION BY CERTIFICATE ApiEncryptionCert; SELECT TOP 1 @AccessToken = CONVERT(NVARCHAR(MAX), DECRYPTBYKEY(AccessToken)), @TokenType = TokenType FROM dbo.ApiTokenStore WHERE ConfigId = @ConfigId AND IsValid = 1 AND ExpiresAt > DATEADD(MINUTE, 2, GETDATE()) -- 2 minute buffer ORDER BY AcquiredAt DESC; CLOSE SYMMETRIC KEY ApiSymmetricKey; IF @AccessToken IS NOT NULL BEGIN SET @Success = 1; -- Log successful token retrieval INSERT INTO dbo.ApiAuditLog ( ConfigId, ApiName, Endpoint, HttpMethod, StatusCode, DurationMs, CorrelationId, Success ) VALUES ( @ConfigId, @ApiName, 'TokenCache', 'GET', 200, DATEDIFF(MILLISECOND, @StartTime, GETDATE()), @CorrelationId, 1 ); RETURN; END END -- Prepare token request DECLARE @TokenUrl NVARCHAR(1000) = @BaseUrl + @TokenEndpoint; DECLARE @Payload NVARCHAR(MAX); DECLARE @Headers NVARCHAR(MAX); -- Different payloads based on grant type IF @GrantType = 'password' AND @ApiName = 'ReqResAPI' BEGIN SET @Payload = JSON_OBJECT( 'email': @ClientId, 'password': @ClientSecret ); SET @Headers = '{"Content-Type":"application/json"}'; END ELSE IF @GrantType = 'client_credentials' BEGIN SET @Payload = 'grant_type=client_credentials&client_id=' + @ClientId + '&client_secret=' + @ClientSecret; SET @Headers = '{"Content-Type":"application/x-www-form-urlencoded"}'; END ELSE BEGIN SET @ErrorMessage = 'Unsupported grant type: ' + ISNULL(@GrantType, 'NULL'); THROW 50003, @ErrorMessage, 1; END -- Retry loop with exponential backoff WHILE @RetryCount < @MaxRetries AND @Success = 0 BEGIN BEGIN TRY -- Update rate limit EXEC dbo.UpdateRateLimit @ConfigId, @TokenEndpoint; -- Make API call with timeout EXEC sp_invoke_external_rest_endpoint @url = @TokenUrl, @method = 'POST', @headers = @Headers, @payload = @Payload, @timeout = 30, @response = @TokenResponse OUTPUT, @response_headers = @ResponseHeaders OUTPUT, @response_code = @StatusCode OUTPUT; -- Log the attempt INSERT INTO dbo.ApiAuditLog ( ConfigId, ApiName, Endpoint, HttpMethod, StatusCode, DurationMs, CorrelationId, Success ) VALUES ( @ConfigId, @ApiName, @TokenEndpoint, 'POST', @StatusCode, DATEDIFF(MILLISECOND, @StartTime, GETDATE()), @CorrelationId, CASE WHEN @StatusCode = 200 THEN 1 ELSE 0 END ); IF @StatusCode = 200 BEGIN -- Parse and encrypt token DECLARE @ExpiresIn INT = JSON_VALUE(@TokenResponse, '$.expires_in'); DECLARE @RefreshToken NVARCHAR(MAX) = JSON_VALUE(@TokenResponse, '$.refresh_token'); SET @AccessToken = JSON_VALUE(@TokenResponse, '$.access_token'); SET @TokenType = JSON_VALUE(@TokenResponse, '$.token_type'); SET @Scope = JSON_VALUE(@TokenResponse, '$.scope'); -- Encrypt and store token OPEN SYMMETRIC KEY ApiSymmetricKey DECRYPTION BY CERTIFICATE ApiEncryptionCert; -- Invalidate previous tokens UPDATE dbo.ApiTokenStore SET IsValid = 0 WHERE ConfigId = @ConfigId; -- Store new token INSERT INTO dbo.ApiTokenStore ( ConfigId, AccessToken, TokenType, ExpiresIn, RefreshToken, Scope, ExpiresAt ) VALUES ( @ConfigId, ENCRYPTBYKEY(KEY_GUID('ApiSymmetricKey'), @AccessToken), @TokenType, @ExpiresIn, ENCRYPTBYKEY(KEY_GUID('ApiSymmetricKey'), ISNULL(@RefreshToken, '')), @Scope, DATEADD(SECOND, ISNULL(@ExpiresIn, 1800), GETDATE()) ); CLOSE SYMMETRIC KEY ApiSymmetricKey; SET @Success = 1; END ELSE BEGIN -- Log error INSERT INTO dbo.ApiErrorLog ( ConfigId, ApiName, Endpoint, HttpMethod, ErrorMessage, StatusCode, ResponseBody, RequestHeaders, ResponseHeaders, CorrelationId, RetryCount ) VALUES ( @ConfigId, @ApiName, @TokenUrl, 'POST', 'Token acquisition failed', @StatusCode, LEFT(@TokenResponse, 4000), @Headers, @ResponseHeaders, @CorrelationId, @RetryCount ); SET @RetryCount = @RetryCount + 1; -- Exponential backoff IF @RetryCount < @MaxRetries WAITFOR DELAY '00:00:' + RIGHT('00' + CAST(POWER(2, @RetryCount) AS VARCHAR(2)), 2); END END TRY BEGIN CATCH SET @ErrorMessage = ERROR_MESSAGE(); INSERT INTO dbo.ApiErrorLog ( ConfigId, ApiName, Endpoint, HttpMethod, ErrorMessage, StackTrace, CorrelationId, RetryCount ) VALUES ( @ConfigId, @ApiName, @TokenUrl, 'POST', @ErrorMessage, ERROR_PROCEDURE() + ' Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)), @CorrelationId, @RetryCount ); SET @RetryCount = @RetryCount + 1; IF @RetryCount < @MaxRetries WAITFOR DELAY '00:00:' + RIGHT('00' + CAST(POWER(2, @RetryCount) AS VARCHAR(2)), 2); END CATCH END IF @Success = 0 BEGIN SET @ErrorMessage = 'Failed to acquire token after ' + CAST(@MaxRetries AS NVARCHAR) + ' attempts'; THROW 50004, @ErrorMessage, 1; END END TRY BEGIN CATCH SET @ErrorMessage = ERROR_MESSAGE(); INSERT INTO dbo.ApiErrorLog ( ConfigId, ApiName, Endpoint, HttpMethod, ErrorMessage, StackTrace, CorrelationId ) VALUES ( @ConfigId, @ApiName, 'Token Acquisition', 'POST', @ErrorMessage, ERROR_PROCEDURE() + ' Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)), @CorrelationId ); THROW; END CATCH END
5. Rate Limiting Functions
CREATE OR ALTER FUNCTION dbo.CheckRateLimit (@ConfigId INT, @Endpoint NVARCHAR(500)) RETURNS BIT AS BEGIN DECLARE @Result BIT = 1; DECLARE @MaxRequests INT, @CurrentCount INT, @LastReset DATETIME; SELECT TOP 1 @MaxRequests = ISNULL(rl.MaxRequestsPerMinute, c.RateLimitPerMinute), @CurrentCount = rl.CurrentRequestCount, @LastReset = rl.LastResetTime FROM dbo.ApiConfig c LEFT JOIN dbo.ApiRateLimit rl ON c.ConfigId = rl.ConfigId AND rl.EndpointPattern = @Endpoint AND rl.IsActive = 1 WHERE c.ConfigId = @ConfigId; -- Reset counter if more than 1 minute has passed IF DATEDIFF(SECOND, @LastReset, GETDATE()) > 60 BEGIN UPDATE dbo.ApiRateLimit SET CurrentRequestCount = 1, LastResetTime = GETDATE() WHERE ConfigId = @ConfigId AND EndpointPattern = @Endpoint; RETURN 1; END -- Check if limit exceeded IF @CurrentCount >= @MaxRequests SET @Result = 0; RETURN @Result; END
CREATE OR ALTER PROCEDURE dbo.UpdateRateLimit @ConfigId INT, @Endpoint NVARCHAR(500) AS BEGIN SET NOCOUNT ON; IF NOT EXISTS ( SELECT 1 FROM dbo.ApiRateLimit WHERE ConfigId = @ConfigId AND EndpointPattern = @Endpoint ) BEGIN INSERT INTO dbo.ApiRateLimit (ConfigId, EndpointPattern) VALUES (@ConfigId, @Endpoint); END UPDATE dbo.ApiRateLimit SET CurrentRequestCount = CurrentRequestCount + 1 WHERE ConfigId = @ConfigId AND EndpointPattern = @Endpoint AND DATEDIFF(SECOND, LastResetTime, GETDATE()) <= 60; END
6. GET API Call with Complex Nested JSON Handling
CREATE OR ALTER PROCEDURE dbo.CallSecureGetApi @ConfigId INT, @Endpoint NVARCHAR(500), @QueryParams NVARCHAR(MAX) = NULL, @BatchId UNIQUEIDENTIFIER = NULL OUTPUT, @CorrelationId UNIQUEIDENTIFIER = NULL OUTPUT AS BEGIN SET NOCOUNT ON; SET @CorrelationId = ISNULL(@CorrelationId, NEWID()); SET @BatchId = ISNULL(@BatchId, NEWID()); DECLARE @StartTime DATETIME = GETDATE(); DECLARE @ErrorMessage NVARCHAR(MAX); DECLARE @AccessToken NVARCHAR(MAX); DECLARE @TokenType NVARCHAR(50); DECLARE @ApiName NVARCHAR(100); DECLARE @BaseUrl NVARCHAR(500); DECLARE @FullUrl NVARCHAR(2000); DECLARE @Headers NVARCHAR(MAX); DECLARE @ResponseJson NVARCHAR(MAX); DECLARE @ResponseHeaders NVARCHAR(MAX); DECLARE @StatusCode INT; DECLARE @RequestSize INT; DECLARE @ResponseSize INT; BEGIN TRY -- Get API configuration SELECT @ApiName = ApiName, @BaseUrl = BaseUrl FROM dbo.ApiConfig WHERE ConfigId = @ConfigId; -- Build URL SET @FullUrl = @BaseUrl + @Endpoint; IF @QueryParams IS NOT NULL SET @FullUrl = @FullUrl + '?' + @QueryParams; -- Check rate limit IF dbo.CheckRateLimit(@ConfigId, @Endpoint) = 0 BEGIN SET @ErrorMessage = 'Rate limit exceeded for endpoint: ' + @Endpoint; THROW 50005, @ErrorMessage, 1; END -- Get JWT token if required IF EXISTS (SELECT 1 FROM dbo.ApiConfig WHERE ConfigId = @ConfigId AND AuthEndpoint IS NOT NULL) BEGIN EXEC dbo.GetSecureApiToken @ConfigId = @ConfigId, @AccessToken = @AccessToken OUTPUT, @TokenType = @TokenType OUTPUT, @CorrelationId = @CorrelationId OUTPUT, @ErrorMessage = @ErrorMessage OUTPUT; IF @AccessToken IS NULL THROW 50006, @ErrorMessage, 1; SET @Headers = JSON_OBJECT( 'Content-Type': 'application/json', 'Authorization': @TokenType + ' ' + @AccessToken, 'Accept': 'application/json', 'X-Correlation-ID': CAST(@CorrelationId AS NVARCHAR(50)), 'User-Agent': 'SQL-Server-2025-API-Client/1.0' ); END ELSE BEGIN SET @Headers = JSON_OBJECT( 'Content-Type': 'application/json', 'Accept': 'application/json', 'X-Correlation-ID': CAST(@CorrelationId AS NVARCHAR(50)) ); END -- Update rate limit EXEC dbo.UpdateRateLimit @ConfigId, @Endpoint; -- Make API call EXEC sp_invoke_external_rest_endpoint @url = @FullUrl, @method = 'GET', @headers = @Headers, @timeout = 45, @response = @ResponseJson OUTPUT, @response_headers = @ResponseHeaders OUTPUT, @response_code = @StatusCode OUTPUT; SET @ResponseSize = LEN(ISNULL(@ResponseJson, '')); -- Log audit INSERT INTO dbo.ApiAuditLog ( ConfigId, ApiName, Endpoint, HttpMethod, StatusCode, RequestSizeBytes, ResponseSizeBytes, DurationMs, CorrelationId, Success ) VALUES ( @ConfigId, @ApiName, @Endpoint, 'GET', @StatusCode, @RequestSize, @ResponseSize, DATEDIFF(MILLISECOND, @StartTime, GETDATE()), @CorrelationId, CASE WHEN @StatusCode BETWEEN 200 AND 299 THEN 1 ELSE 0 END ); IF @StatusCode = 200 BEGIN -- Store response INSERT INTO dbo.ApiResponseStage ( BatchId, ConfigId, Endpoint, ResponseJson, ResponseHeaders, StatusCode, CreatedDate ) VALUES ( @BatchId, @ConfigId, @FullUrl, @ResponseJson, @ResponseHeaders, @StatusCode, GETDATE() ); -- Return success SELECT @CorrelationId AS CorrelationId, @BatchId AS BatchId, @StatusCode AS StatusCode, 'Success' AS Status, @ResponseSize AS ResponseSizeBytes, DATEDIFF(MILLISECOND, @StartTime, GETDATE()) AS DurationMs; END ELSE BEGIN -- Log error INSERT INTO dbo.ApiErrorLog ( ConfigId, ApiName, Endpoint, HttpMethod, ErrorMessage, StatusCode, ResponseBody, ResponseHeaders, RequestHeaders, CorrelationId, BatchId ) VALUES ( @ConfigId, @ApiName, @FullUrl, 'GET', 'API call failed', @StatusCode, LEFT(@ResponseJson, 4000), @ResponseHeaders, @Headers, @CorrelationId, @BatchId ); THROW 50007, 'API call failed with status: ' + CAST(@StatusCode AS NVARCHAR(10)), 1; END END TRY BEGIN CATCH SET @ErrorMessage = ERROR_MESSAGE(); INSERT INTO dbo.ApiErrorLog ( ConfigId, ApiName, Endpoint, HttpMethod, ErrorMessage, StackTrace, CorrelationId, BatchId ) VALUES ( @ConfigId, @ApiName, @Endpoint, 'GET', @ErrorMessage, ERROR_PROCEDURE() + ' Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)), @CorrelationId, @BatchId ); THROW; END CATCH END
7. POST API Call with Complex JSON
CREATE OR ALTER PROCEDURE dbo.CallSecurePostApi @ConfigId INT, @Endpoint NVARCHAR(500), @JsonPayload NVARCHAR(MAX), @BatchId UNIQUEIDENTIFIER = NULL OUTPUT, @CorrelationId UNIQUEIDENTIFIER = NULL OUTPUT, @ResponseJson NVARCHAR(MAX) = NULL OUTPUT AS BEGIN SET NOCOUNT ON; SET @CorrelationId = ISNULL(@CorrelationId, NEWID()); SET @BatchId = ISNULL(@BatchId, NEWID()); DECLARE @StartTime DATETIME = GETDATE(); DECLARE @ErrorMessage NVARCHAR(MAX); DECLARE @AccessToken NVARCHAR(MAX); DECLARE @TokenType NVARCHAR(50); DECLARE @ApiName NVARCHAR(100); DECLARE @BaseUrl NVARCHAR(500); DECLARE @FullUrl NVARCHAR(2000); DECLARE @Headers NVARCHAR(MAX); DECLARE @ResponseHeaders NVARCHAR(MAX); DECLARE @StatusCode INT; DECLARE @RequestSize INT = LEN(@JsonPayload); DECLARE @ResponseSize INT; BEGIN TRY -- Validate JSON IF ISJSON(@JsonPayload) = 0 BEGIN SET @ErrorMessage = 'Invalid JSON payload'; THROW 50008, @ErrorMessage, 1; END -- Get API configuration SELECT @ApiName = ApiName, @BaseUrl = BaseUrl FROM dbo.ApiConfig WHERE ConfigId = @ConfigId; SET @FullUrl = @BaseUrl + @Endpoint; -- Check rate limit IF dbo.CheckRateLimit(@ConfigId, @Endpoint) = 0 BEGIN SET @ErrorMessage = 'Rate limit exceeded for endpoint: ' + @Endpoint; THROW 50009, @ErrorMessage, 1; END -- Get JWT token if required IF EXISTS (SELECT 1 FROM dbo.ApiConfig WHERE ConfigId = @ConfigId AND AuthEndpoint IS NOT NULL) BEGIN EXEC dbo.GetSecureApiToken @ConfigId = @ConfigId, @AccessToken = @AccessToken OUTPUT, @TokenType = @TokenType OUTPUT, @CorrelationId = @CorrelationId OUTPUT, @ErrorMessage = @ErrorMessage OUTPUT; IF @AccessToken IS NULL THROW 50010, @ErrorMessage, 1; SET @Headers = JSON_OBJECT( 'Content-Type': 'application/json', 'Authorization': @TokenType + ' ' + @AccessToken, 'Accept': 'application/json', 'X-Correlation-ID': CAST(@CorrelationId AS NVARCHAR(50)), 'User-Agent': 'SQL-Server-2025-API-Client/1.0' ); END ELSE BEGIN SET @Headers = JSON_OBJECT( 'Content-Type': 'application/json', 'Accept': 'application/json', 'X-Correlation-ID': CAST(@CorrelationId AS NVARCHAR(50)) ); END -- Update rate limit EXEC dbo.UpdateRateLimit @ConfigId, @Endpoint; -- Make API call EXEC sp_invoke_external_rest_endpoint @url = @FullUrl, @method = 'POST', @headers = @Headers, @payload = @JsonPayload, @timeout = 60, @response = @ResponseJson OUTPUT, @response_headers = @ResponseHeaders OUTPUT, @response_code = @StatusCode OUTPUT; SET @ResponseSize = LEN(ISNULL(@ResponseJson, '')); -- Log audit INSERT INTO dbo.ApiAuditLog ( ConfigId, ApiName, Endpoint, HttpMethod, StatusCode, RequestSizeBytes, ResponseSizeBytes, DurationMs, CorrelationId, Success ) VALUES ( @ConfigId, @ApiName, @Endpoint, 'POST', @StatusCode, @RequestSize, @ResponseSize, DATEDIFF(MILLISECOND, @StartTime, GETDATE()), @CorrelationId, CASE WHEN @StatusCode BETWEEN 200 AND 299 THEN 1 ELSE 0 END ); IF @StatusCode BETWEEN 200 AND 299 BEGIN -- Store response INSERT INTO dbo.ApiResponseStage ( BatchId, ConfigId, Endpoint, ResponseJson, ResponseHeaders, StatusCode, CreatedDate ) VALUES ( @BatchId, @ConfigId, @FullUrl, @ResponseJson, @ResponseHeaders, @StatusCode, GETDATE() ); -- Return success SELECT @CorrelationId AS CorrelationId, @BatchId AS BatchId, @StatusCode AS StatusCode, 'Success' AS Status, @RequestSize AS RequestSizeBytes, @ResponseSize AS ResponseSizeBytes, DATEDIFF(MILLISECOND, @StartTime, GETDATE()) AS DurationMs, @ResponseJson AS ResponseJson; END ELSE BEGIN -- Log error INSERT INTO dbo.ApiErrorLog ( ConfigId, ApiName, Endpoint, HttpMethod, ErrorMessage, StatusCode, ResponseBody, ResponseHeaders, RequestHeaders, RequestPayload, CorrelationId, BatchId ) VALUES ( @ConfigId, @ApiName, @FullUrl, 'POST', 'API call failed', @StatusCode, LEFT(@ResponseJson, 4000), @ResponseHeaders, @Headers, LEFT(@JsonPayload, 4000), @CorrelationId, @BatchId ); THROW 50011, 'API call failed with status: ' + CAST(@StatusCode AS NVARCHAR(10)), 1; END END TRY BEGIN CATCH SET @ErrorMessage = ERROR_MESSAGE(); INSERT INTO dbo.ApiErrorLog ( ConfigId, ApiName, Endpoint, HttpMethod, ErrorMessage, StackTrace, RequestPayload, CorrelationId, BatchId ) VALUES ( @ConfigId, @ApiName, @Endpoint, 'POST', @ErrorMessage, ERROR_PROCEDURE() + ' Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)), LEFT(@JsonPayload, 4000), @CorrelationId, @BatchId ); THROW; END CATCH END
8. Process Complex Nested JSON from Free APIs
CREATE OR ALTER PROCEDURE dbo.ProcessJSONPlaceholderData @BatchId UNIQUEIDENTIFIER, @CorrelationId UNIQUEIDENTIFIER = NULL AS BEGIN SET NOCOUNT ON; SET @CorrelationId = ISNULL(@CorrelationId, NEWID()); DECLARE @ErrorMessage NVARCHAR(MAX); DECLARE @ProcessedCount INT = 0; DECLARE @ErrorCount INT = 0; BEGIN TRY -- Process users with nested data ;WITH UserData AS ( SELECT StageId, JSON_VALUE(ResponseJson, '$.id') AS UserId, JSON_VALUE(ResponseJson, '$.name') AS UserName, JSON_VALUE(ResponseJson, '$.username') AS Username, JSON_VALUE(ResponseJson, '$.email') AS Email, JSON_VALUE(ResponseJson, '$.address.street') AS AddressStreet, JSON_VALUE(ResponseJson, '$.address.suite') AS AddressSuite, JSON_VALUE(ResponseJson, '$.address.city') AS AddressCity, JSON_VALUE(ResponseJson, '$.address.zipcode') AS AddressZipCode, JSON_VALUE(ResponseJson, '$.address.geo.lat') AS GeoLat, JSON_VALUE(ResponseJson, '$.address.geo.lng') AS GeoLng, JSON_VALUE(ResponseJson, '$.phone') AS Phone, JSON_VALUE(ResponseJson, '$.website') AS Website, JSON_VALUE(ResponseJson, '$.company.name') AS CompanyName, JSON_VALUE(ResponseJson, '$.company.catchPhrase') AS CompanyCatchPhrase, JSON_VALUE(ResponseJson, '$.company.bs') AS CompanyBs, ResponseJson AS RawResponse FROM dbo.ApiResponseStage WHERE BatchId = @BatchId AND Endpoint LIKE '%/users/%' AND ISJSON(ResponseJson) = 1 ) MERGE dbo.UsersData AS target USING UserData AS source ON target.UserId = source.UserId WHEN MATCHED THEN UPDATE SET Email = source.Email, FirstName = CASE WHEN CHARINDEX(' ', source.UserName) > 0 THEN LEFT(source.UserName, CHARINDEX(' ', source.UserName) - 1) ELSE source.UserName END, LastName = CASE WHEN CHARINDEX(' ', source.UserName) > 0 THEN SUBSTRING(source.UserName, CHARINDEX(' ', source.UserName) + 1, LEN(source.UserName)) ELSE '' END, AddressStreet = source.AddressStreet, AddressSuite = source.AddressSuite, AddressCity = source.AddressCity, AddressZipCode = source.AddressZipCode, AddressGeoLat = TRY_CAST(source.GeoLat AS DECIMAL(10,6)), AddressGeoLng = TRY_CAST(source.GeoLng AS DECIMAL(10,6)), Phone = source.Phone, Website = source.Website, CompanyName = source.CompanyName, CompanyCatchPhrase = source.CompanyCatchPhrase, CompanyBs = source.CompanyBs, RawResponse = source.RawResponse, LastSynced = GETDATE() WHEN NOT MATCHED THEN INSERT ( UserId, Email, FirstName, LastName, AddressStreet, AddressSuite, AddressCity, AddressZipCode, AddressGeoLat, AddressGeoLng, Phone, Website, CompanyName, CompanyCatchPhrase, CompanyBs, RawResponse ) VALUES ( source.UserId, source.Email, CASE WHEN CHARINDEX(' ', source.UserName) > 0 THEN LEFT(source.UserName, CHARINDEX(' ', source.UserName) - 1) ELSE source.UserName END, CASE WHEN CHARINDEX(' ', source.UserName) > 0 THEN SUBSTRING(source.UserName, CHARINDEX(' ', source.UserName) + 1, LEN(source.UserName)) ELSE '' END, source.AddressStreet, source.AddressSuite, source.AddressCity, source.AddressZipCode, TRY_CAST(source.GeoLat AS DECIMAL(10,6)), TRY_CAST(source.GeoLng AS DECIMAL(10,6)), source.Phone, source.Website, source.CompanyName, source.CompanyCatchPhrase, source.CompanyBs, source.RawResponse ); SET @ProcessedCount = @@ROWCOUNT; -- Process posts for each user INSERT INTO dbo.UsersPosts (UserId, PostId, Title, Body, BatchId) SELECT JSON_VALUE(value, '$.userId') AS UserId, JSON_VALUE(value, '$.id') AS PostId, JSON_VALUE(value, '$.title') AS Title, JSON_VALUE(value, '$.body') AS Body, @BatchId FROM dbo.ApiResponseStage s CROSS APPLY OPENJSON(s.ResponseJson) WHERE s.BatchId = @BatchId AND s.Endpoint LIKE '%/posts' AND ISJSON(s.ResponseJson) = 1; -- Process todos for each user INSERT INTO dbo.UsersTodos (UserId, TodoId, Title, Completed, BatchId) SELECT JSON_VALUE(value, '$.userId') AS UserId, JSON_VALUE(value, '$.id') AS TodoId, JSON_VALUE(value, '$.title') AS Title, JSON_VALUE(value, '$.completed') AS Completed, @BatchId FROM dbo.ApiResponseStage s CROSS APPLY OPENJSON(s.ResponseJson) WHERE s.BatchId = @BatchId AND s.Endpoint LIKE '%/todos' AND ISJSON(s.ResponseJson) = 1; -- Update staging table UPDATE dbo.ApiResponseStage SET IsProcessed = 1, ProcessedDate = GETDATE() WHERE BatchId = @BatchId; -- Log success INSERT INTO dbo.ApiAuditLog ( ConfigId, ApiName, Endpoint, HttpMethod, StatusCode, DurationMs, CorrelationId, Success ) VALUES ( (SELECT TOP 1 ConfigId FROM dbo.ApiResponseStage WHERE BatchId = @BatchId), 'JSONPlaceholder', 'ProcessData', 'PROCESS', 200, DATEDIFF(MILLISECOND, GETDATE(), GETDATE()), @CorrelationId, 1 ); PRINT 'Successfully processed ' + CAST(@ProcessedCount AS NVARCHAR) + ' users'; END TRY BEGIN CATCH SET @ErrorMessage = ERROR_MESSAGE(); SET @ErrorCount = @ErrorCount + 1; INSERT INTO dbo.ApiErrorLog ( ApiName, Endpoint, HttpMethod, ErrorMessage, StackTrace, CorrelationId, BatchId ) VALUES ( 'JSONPlaceholder', 'ProcessData', 'PROCESS', @ErrorMessage, ERROR_PROCEDURE() + ' Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)), @CorrelationId, @BatchId ); THROW; END CATCH END
9. Complete Example Usage
A. GET Request to JSONPlaceholder
-- Example 1: GET users with nested data DECLARE @BatchId UNIQUEIDENTIFIER, @CorrelationId UNIQUEIDENTIFIER; EXEC dbo.CallSecureGetApi @ConfigId = (SELECT ConfigId FROM dbo.ApiConfig WHERE ApiName = 'JSONPlaceholder'), @Endpoint = 'users', @QueryParams = '_limit=10', @BatchId = @BatchId OUTPUT, @CorrelationId = @CorrelationId OUTPUT; -- Process the nested JSON data EXEC dbo.ProcessJSONPlaceholderData @BatchId = @BatchId; -- View processed data SELECT TOP 10 * FROM dbo.UsersData ORDER BY UserId;
B. GET Request with JWT Authentication (ReqRes.in)
-- Example 2: GET with JWT authentication DECLARE @BatchId2 UNIQUEIDENTIFIER, @CorrelationId2 UNIQUEIDENTIFIER; -- First, authenticate to get token (automatically handled) EXEC dbo.CallSecureGetApi @ConfigId = (SELECT ConfigId FROM dbo.ApiConfig WHERE ApiName = 'ReqResAPI'), @Endpoint = 'users', @QueryParams = 'page=2', @BatchId = @BatchId2 OUTPUT, @CorrelationId = @CorrelationId2 OUTPUT; -- View the response SELECT TOP 5 JSON_VALUE(ResponseJson, '$.data[0].id') AS FirstUserId, JSON_VALUE(ResponseJson, '$.data[0].email') AS FirstUserEmail, JSON_VALUE(ResponseJson, '$.page') AS Page, JSON_VALUE(ResponseJson, '$.total_pages') AS TotalPages FROM dbo.ApiResponseStage WHERE BatchId = @BatchId2;
C. POST Request with Complex JSON
-- Example 3: POST with nested JSON payload DECLARE @BatchId3 UNIQUEIDENTIFIER, @CorrelationId3 UNIQUEIDENTIFIER, @ResponseJson NVARCHAR(MAX); DECLARE @JsonPayload NVARCHAR(MAX) = JSON_OBJECT( 'name': 'John Doe', 'job': 'Software Engineer', 'contact': JSON_OBJECT( 'email': 'john.doe@example.com', 'phone': '+1234567890' ), 'address': JSON_OBJECT( 'street': '123 Main St', 'city': 'New York', 'state': 'NY', 'zip': '10001' ), 'skills': JSON_ARRAY('C#', 'SQL', 'JavaScript', 'Python') ); EXEC dbo.CallSecurePostApi @ConfigId = (SELECT ConfigId FROM dbo.ApiConfig WHERE ApiName = 'ReqResAPI'), @Endpoint = 'users', @JsonPayload = @JsonPayload, @BatchId = @BatchId3 OUTPUT, @CorrelationId = @CorrelationId3 OUTPUT, @ResponseJson = @ResponseJson OUTPUT; -- View response SELECT JSON_VALUE(@ResponseJson, '$.id') AS CreatedId, JSON_VALUE(@ResponseJson, '$.name') AS Name, JSON_VALUE(@ResponseJson, '$.job') AS Job, JSON_VALUE(@ResponseJson, '$.createdAt') AS CreatedAt;
10. Monitoring and Dashboard Views
-- Comprehensive monitoring view CREATE OR ALTER VIEW dbo.vw_ApiHealthDashboard AS WITH ApiStats AS ( SELECT c.ApiName, c.BaseUrl, COUNT(DISTINCT s.BatchId) AS TotalBatches, COUNT(s.StageId) AS TotalRequests, SUM(CASE WHEN s.StatusCode BETWEEN 200 AND 299 THEN 1 ELSE 0 END) AS SuccessfulRequests, SUM(CASE WHEN s.StatusCode >= 400 THEN 1 ELSE 0 END) AS FailedRequests, AVG(DATEDIFF(MILLISECOND, s.CreatedDate, ISNULL(s.ProcessedDate, GETDATE()))) AS AvgProcessingTimeMs, MIN(s.CreatedDate) AS FirstRequest, MAX(s.CreatedDate) AS LastRequest FROM dbo.ApiConfig c LEFT JOIN dbo.ApiResponseStage s ON c.ConfigId = s.ConfigId GROUP BY c.ConfigId, c.ApiName, c.BaseUrl ), ErrorStats AS ( SELECT c.ApiName, COUNT(e.ErrorId) AS TotalErrors, COUNT(CASE WHEN e.Resolved = 0 THEN 1 END) AS UnresolvedErrors, MIN(e.ErrorTime) AS FirstError, MAX(e.ErrorTime) AS LastError FROM dbo.ApiConfig c LEFT JOIN dbo.ApiErrorLog e ON c.ConfigId = e.ConfigId GROUP BY c.ConfigId, c.ApiName ), RateLimitStats AS ( SELECT c.ApiName, rl.EndpointPattern, rl.MaxRequestsPerMinute, rl.CurrentRequestCount, rl.LastResetTime FROM dbo.ApiConfig c LEFT JOIN dbo.ApiRateLimit rl ON c.ConfigId = rl.ConfigId ) SELECT s.ApiName, s.BaseUrl, s.TotalBatches, s.TotalRequests, s.SuccessfulRequests, s.FailedRequests, s.AvgProcessingTimeMs, s.FirstRequest, s.LastRequest, e.TotalErrors, e.UnresolvedErrors, e.FirstError, e.LastError, CASE WHEN s.TotalRequests > 0 THEN CAST((s.SuccessfulRequests * 100.0 / s.TotalRequests) AS DECIMAL(5,2)) ELSE 100.0 END AS SuccessRate, DATEDIFF(HOUR, s.LastRequest, GETDATE()) AS HoursSinceLastRequest FROM ApiStats s LEFT JOIN ErrorStats e ON s.ApiName = e.ApiName;
11. Cleanup and Maintenance Job
CREATE OR ALTER PROCEDURE dbo.CleanupApiData @RetentionDays INT = 30, @BatchSize INT = 5000, @DryRun BIT = 0 AS BEGIN SET NOCOUNT ON; DECLARE @CutoffDate DATETIME = DATEADD(DAY, -@RetentionDays, GETDATE()); DECLARE @RowsDeleted INT; DECLARE @TotalDeleted INT = 0; DECLARE @Message NVARCHAR(MAX); IF @DryRun = 1 BEGIN -- Show what would be deleted SELECT 'ApiResponseStage' AS TableName, COUNT(*) AS RecordsToDelete FROM dbo.ApiResponseStage WHERE CreatedDate < @CutoffDate AND IsProcessed = 1 UNION ALL SELECT 'ApiErrorLog' AS TableName, COUNT(*) AS RecordsToDelete FROM dbo.ApiErrorLog WHERE ErrorTime < @CutoffDate AND Resolved = 1 UNION ALL SELECT 'ApiAuditLog' AS TableName, COUNT(*) AS RecordsToDelete FROM dbo.ApiAuditLog WHERE CalledAt < @CutoffDate; RETURN; END BEGIN TRANSACTION; BEGIN TRY -- Cleanup ApiResponseStage in batches SET @RowsDeleted = 1; WHILE @RowsDeleted > 0 BEGIN DELETE TOP (@BatchSize) FROM dbo.ApiResponseStage WHERE CreatedDate < @CutoffDate AND IsProcessed = 1; SET @RowsDeleted = @@ROWCOUNT; SET @TotalDeleted = @TotalDeleted + @RowsDeleted; WAITFOR DELAY '00:00:00.05'; END SET @Message = 'Deleted ' + CAST(@TotalDeleted AS NVARCHAR) + ' records from ApiResponseStage'; PRINT @Message; -- Cleanup resolved errors DELETE FROM dbo.ApiErrorLog WHERE ErrorTime < @CutoffDate AND Resolved = 1; SET @Message = 'Deleted ' + CAST(@@ROWCOUNT AS NVARCHAR) + ' records from ApiErrorLog'; PRINT @Message; -- Cleanup old audit logs DELETE FROM dbo.ApiAuditLog WHERE CalledAt < @CutoffDate; SET @Message = 'Deleted ' + CAST(@@ROWCOUNT AS NVARCHAR) + ' records from ApiAuditLog'; PRINT @Message; COMMIT TRANSACTION; -- Log cleanup INSERT INTO dbo.ApiAuditLog ( ApiName, Endpoint, HttpMethod, StatusCode, DurationMs, Success ) VALUES ( 'System', 'Cleanup', 'DELETE', 200, 0, 1 ); END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE(); INSERT INTO dbo.ApiErrorLog ( ApiName, Endpoint, HttpMethod, ErrorMessage, StackTrace ) VALUES ( 'System', 'Cleanup', 'DELETE', @ErrorMessage, ERROR_PROCEDURE() + ' Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)) ); THROW; END CATCH END
Key Security Features:
Encryption at Rest: All sensitive data (tokens, credentials) encrypted using SQL Server encryption
JWT Management: Secure token acquisition, storage, and refresh with encryption
Rate Limiting: Prevents API abuse and respects API limits
Comprehensive Logging: Full audit trail for compliance and debugging
Error Handling: Robust error capture with correlation IDs for tracing
Parameterized Inputs: Protection against SQL injection
Timeouts: Prevents hanging requests
Retry Logic: Exponential backoff for transient failures
Correlation IDs: End-to-end request tracing
Compression: Efficient storage of large JSON responses
Real API Examples Used:
JSONPlaceholder:
https://jsonplaceholder.typicode.com//users- Returns nested user data with addresses and companies/posts- User posts data/todos- User todos data
ReqRes.in:
https://reqres.in/api//login- JWT authentication endpoint/users- User data with pagination/users/{id}- Single user with nested data
Best Use Cases
Enterprise Scenarios Where This Excels:
1. E-commerce Data Synchronization
-- Sync customer orders from multiple marketplaces EXEC dbo.OrchestrateApiDataSync @ConfigId = 1, @Endpoint = '/orders', @SinceDate = DATEADD(HOUR, -1, GETDATE());
2. Financial Data Aggregation
Real-time stock price updates
Currency exchange rate synchronization
Transaction processing from banking APIs
3. IoT Data Ingestion
-- Process sensor data from IoT devices INSERT INTO SensorReadings SELECT JSON_VALUE(value, '$.deviceId'), JSON_VALUE(value, '$.temperature'), JSON_VALUE(value, '$.timestamp') FROM OPENJSON(@ResponseJson, '$.readings');
4. Customer Relationship Management
Sync contact data from CRM systems
Update customer profiles in real-time
Track customer interactions across platforms
5. Supply Chain Integration
Inventory level synchronization
Order status updates
Shipping tracking integration
7. Handling Complex Scenarios
7.1 Pagination Strategies
-- Handle APIs with different pagination models DECLARE @CurrentPage INT = 1; DECLARE @HasMore BIT = 1; WHILE @HasMore = 1 AND @CurrentPage <= @MaxPages BEGIN -- Build URL with appropriate pagination params SET @PageUrl = @BaseUrl + @Endpoint + CASE @PaginationType WHEN 'page_based' THEN '?page=' + CAST(@CurrentPage AS NVARCHAR) WHEN 'offset_based' THEN '?offset=' + CAST((@CurrentPage-1)*@PageSize AS NVARCHAR) WHEN 'cursor_based' THEN '?cursor=' + @NextCursor END; END
7.2 Rate Limiting & Throttling
-- Implement adaptive rate limiting IF @StatusCode = 429 -- Too Many Requests BEGIN DECLARE @RetryAfter INT = ISNULL( JSON_VALUE(@ResponseHeaders, '$.Retry-After'), 60 ); WAITFOR DELAY '00:00:' + RIGHT('00' + CAST(@RetryAfter AS VARCHAR(2)), 2); END
7.3 Data Transformation Pipelines
-- Transform API data for different consumers ;WITH TransformedData AS ( SELECT JSON_VALUE(value, '$.id') AS SourceId, UPPER(JSON_VALUE(value, '$.name')) AS NormalizedName, FORMAT(TRY_CAST(JSON_VALUE(value, '$.date') AS DATETIME), 'yyyy-MM-dd') AS StandardDate, CASE WHEN JSON_VALUE(value, '$.status') = 'active' THEN 1 ELSE 0 END AS IsActive FROM OPENJSON(@ResponseJson, '$.items') ) MERGE TargetTable AS target USING TransformedData AS source...
7.4 Circuit Breaker Pattern
-- Implement circuit breaker for unreliable APIs IF (SELECT FailureCount FROM ApiHealth WHERE ConfigId = @ConfigId) > @Threshold BEGIN -- Circuit open - fail fast RAISERROR('API circuit breaker open - too many failures', 16, 1); RETURN; END
8. Pros & Cons Analysis
Advantages ✅
Performance Benefits:
Reduced Network Latency: Direct database-to-API calls eliminate middle-tier overhead
Bulk Processing: Native SQL operations on large datasets
Connection Pooling: Reuse of database connections for API calls
Development Efficiency:
Single Codebase: SQL-based logic reduces technology stack complexity
Transactional Integrity: ACID compliance for API operations
Simplified Testing: All logic testable within SQL environment
Operational Benefits:
Centralized Management: Single point for API configuration and monitoring
Built-in Security: Leverage SQL Server security features
Scalability: Built on proven SQL Server infrastructure
Challenges & Limitations ❌
Technical Constraints:
Timeout Handling: Limited to SQL Server timeout configurations
Memory Usage: Large JSON responses can consume significant memory
Connection Limits: Shared database connection pool constraints
Development Considerations:
SQL Expertise Required: Developers need strong SQL skills
Limited Debugging: Traditional debugging tools less effective
Version Control: Managing SQL scripts vs. application code
Architectural Concerns:
Database Coupling: Tight coupling between business logic and database
Scalability Limits: Horizontal scaling challenges
Vendor Lock-in: Specific to SQL Server ecosystem
9. Security Measures & Do's/Don'ts
Critical Security Implementations:
DO Implement:
✅ Encryption at Rest OPEN SYMMETRIC KEY ApiSymmetricKey DECRYPTION BY CERTIFICATE ApiEncryptionCert; ✅ Rate Limiting CREATE TABLE dbo.ApiRateLimit ( MaxRequestsPerMinute INT DEFAULT 60, CurrentRequestCount INT ); ✅ Comprehensive Auditing CREATE TABLE dbo.ApiAuditLog ( CorrelationId UNIQUEIDENTIFIER, CalledBy SYSNAME, RequestHeaders NVARCHAR(MAX) ); ✅ Input Validation IF ISJSON(@JsonPayload) = 0 THROW 50008, 'Invalid JSON payload', 1;
DON'T Do:
❌ Hardcode Credentials -- DON'T: INSERT INTO Config VALUES ('token', 'my-secret-key'); -- DO: Use encrypted storage ❌ Ignore Timeouts -- DON'T: @timeout = 0 (infinite) -- DO: @timeout = 30 (reasonable limit) ❌ Skip Error Handling -- DON'T: Assume API calls always succeed -- DO: Implement comprehensive try-catch blocks ❌ Expose Sensitive Data -- DON'T: SELECT * FROM ApiTokenStore -- DO: SELECT TokenType, ExpiresAt FROM ApiTokenStore
Security Checklist:
All secrets encrypted using SQL Server encryption
JWT tokens validated and refreshed automatically
Rate limiting implemented per endpoint
Comprehensive audit logging enabled
Input validation for all parameters
Error messages sanitized for production
Regular security reviews scheduled
Access controls properly configured
10. Advanced Implementation Patterns
10.1 Async Processing with Service Broker
-- Implement async API processing BEGIN CONVERSATION TIMER @ConversationHandle TIMEOUT = 30; -- Process after 30 seconds -- Queue API requests for background processing SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [ApiRequest] (@JsonPayload);
10.2 Caching Strategy
-- Implement API response caching CREATE TABLE dbo.ApiResponseCache ( CacheKey NVARCHAR(500) PRIMARY KEY, ResponseJson NVARCHAR(MAX), ExpiresAt DATETIME, CreatedAt DATETIME DEFAULT GETDATE() ); -- Check cache before API call SELECT @CachedResponse = ResponseJson FROM dbo.ApiResponseCache WHERE CacheKey = @CacheKey AND ExpiresAt > GETDATE();
10.3 Circuit Breaker Implementation
-- Track API health CREATE TABLE dbo.ApiCircuitBreaker ( ConfigId INT PRIMARY KEY, FailureCount INT DEFAULT 0, LastFailureTime DATETIME, CircuitState VARCHAR(20) DEFAULT 'CLOSED', LastStateChange DATETIME ); -- Check circuit state before API call IF (SELECT CircuitState FROM ApiCircuitBreaker WHERE ConfigId = @ConfigId) = 'OPEN' AND DATEADD(MINUTE, 5, LastStateChange) > GETDATE() BEGIN -- Circuit is open - fail fast RETURN; END
11. Conclusion & Final Thoughts
The Future of Database-Driven API Integration
SQL Server 2025 represents a paradigm shift in how we approach API integration. By bringing REST API capabilities directly into the database layer, organizations can build more efficient, secure, and maintainable data pipelines.
Key Takeaways:
Security First: Always implement encryption, rate limiting, and comprehensive logging
Resilience Matters: Build systems that gracefully handle API failures
Monitor Everything: Visibility is crucial for production systems
Start Simple: Begin with basic integrations before implementing complex patterns
Test Thoroughly: Use free APIs for development and testing
Your Next Steps:
Experiment: Set up a test environment with free APIs
Implement: Start with basic GET requests and progress to complex scenarios
Monitor: Build your monitoring dashboard early
Optimize: Continuously refine based on performance metrics
Share: Contribute your learnings back to the community
Free Resources from FreeLearning365.com:
Download complete SQL scripts from our GitHub repository
Join our community forum for support and discussions
Access video tutorials on advanced implementation patterns
Subscribe to our newsletter for weekly SQL Server tips
Final Note from FreeLearning365.com:
This comprehensive guide represents hundreds of hours of research and practical implementation. We believe in democratizing knowledge and making advanced technical skills accessible to everyone. Whether you're a beginner or an experienced professional, there's always something new to learn in the ever-evolving world of technology.
Stay curious, keep learning, and remember: The best investment you can make is in your own knowledge.
💡 Ready to Implement?
Download our complete SQL script package and start building your secure API integration system today! Visit FreeLearning365.com/ for all materials mentioned in this guide.
Share your implementation experiences with our community using #SQLServerAPI #FreeLearning365
.png)
0 Comments
thanks for your comments!