Calling a GET REST API Dynamically in a Loop Using ZappySys Linked Server and SQL Server

ODBC PowerPack JSON Driver
, , ,
1

Overview

In many real-world integration scenarios, a REST API must be called multiple times, once for each value stored in a SQL table. This typically happens when:

  • The API does not support bulk requests
  • Each request requires a different URL parameter
  • You need to execute the API dynamically from SQL Server or SSIS
  • You want to avoid returning multiple result sets

In this article, we’ll demonstrate a generic and reusable pattern to call a GET REST API dynamically in a loop, using ZappySys Linked Server and SQL Server, where request values come from a table containing multiple rows.

Prerequisites

  • SQL Server 2012 or later
  • ZappySys PowerPack installed
  • A configured ZappySys Linked Server
  • A SQL table containing values to drive the API calls (e.g., IDs, codes, keys)

Example source table:

MySourceTable
--------------
KeyValue

Each row in this table will result in one API call.

Solution Approach

The overall approach consists of:

  1. Reading values from a SQL table
  2. Looping through them one by one
  3. Dynamically building the GET API URL
  4. Executing the API via ZappySys Linked Server
  5. Capturing all responses into one final result set

This pattern is safe, scalable, and SSIS-friendly.

Step 1: Load Source Values into a Temporary Table

We first load the values into a temporary table and assign row numbers to support looping.

SELECT 
    KeyValue,
    ROW_NUMBER() OVER (ORDER BY KeyValue) AS rn
INTO #SourceKeys
FROM MySourceTable;

Step 2: Create a Final Result Table

To prevent multiple result sets, we store all API responses in a single table.
The response is captured as raw JSON to keep the solution schema-independent.

CREATE TABLE #FinalResult
(
    KeyValue NVARCHAR(100),
    payload  NVARCHAR(MAX)
);

Step 3: Loop Through the Table and Call the GET API Dynamically

Inside the loop, the API URL is constructed dynamically using the value from the table.

DECLARE
    @CurrentRow INT = 1,
    @MaxRow INT,
    @KeyValue NVARCHAR(100),
    @SQL NVARCHAR(MAX);

SELECT @MaxRow = MAX(rn) FROM #SourceKeys;

WHILE @CurrentRow <= @MaxRow
BEGIN
    -- Get current value
    SELECT @KeyValue = KeyValue
    FROM #SourceKeys
    WHERE rn = @CurrentRow;

    -- Build dynamic GET API query
    SET @SQL = '
    SELECT ''' + @KeyValue + ''' AS KeyValue,
           response AS payload
    FROM "$"
    WITH(
         Src = ''https://api.example.com/resource?key=' + @KeyValue + '''
        ,RequestContentTypeCode = ''ApplicationJson''
        ,Header = ''cache-control: no-cache || Accept: */*''
        ,RequestMethod = ''GET''
        ,PagingByUrlLastPageWhenConditionEqualsTo = 1
        ,PagingByUrlMaxPages = 500
    )';

    -- Execute API call and capture result
    INSERT INTO #FinalResult (KeyValue, payload)
    EXEC (@SQL) AT MyLinkedServer;

    -- Optional delay to avoid API throttling
    -- WAITFOR DELAY ''00:00:01'';

    SET @CurrentRow = @CurrentRow + 1;
END

Step 4: Return a Single Result Set

After the loop completes, return all API responses together.

SELECT *
FROM #FinalResult;

Each row represents:

  • One value from the source table
  • The corresponding API response in JSON format

Conclusion

By combining ZappySys ODBC DSN and Linked Server with dynamic SQL and looping logic, you can easily call any GET REST API dynamically based on data stored in SQL Server. This approach provides maximum flexibility, avoids common result-set issues, and integrates seamlessly with SSIS and downstream data processing pipelines.