When working with REST APIs in reporting or ETL workflows, it’s common to deal with parameters that change dynamically, such as dates, IDs, or filter values. ZappySys ODBC PowerPack makes it easy to call APIs with dynamic parameters using Placeholders or queries. In this article, we’ll show you multiple ways to achieve this using the ZappySys ODBC Driver.
Why Use Dynamic Parameters in API Calls?
APIs often require input parameters like:
- Date ranges (e.g.,
startDate,endDate) - Search filters (e.g.,
status=open) - Authentication tokens
Hardcoding these values is not practical. Instead, dynamic parameters let you automate API requests and integrate them into your tools like SQL Server, PowerBI.
Scenario 1: Using Placeholders in URL or Body
ZappySys ODBC Driver supports the use of built-in placeholders and functions,
which you can use directly in your API URL, Headers, or Request Body.
Simply use the Placeholder Function syntax to inject dynamic values.
Example: Dynamic Date Range in API URL
Suppose your API URL looks like this, and you want to dynamically pass the start date as 7 days ago and end date as today’s date.
To achieve this, you can use ZappySys date placeholders in your URL like below:
https://api.example.com/orders?start_date=<<today-7d||yyyy-MM-dd,FUN_TO_DATE>>&end_date=<<yyyy-MM-dd,FUN_TODAY>>
This will auto-resolve at runtime to something like:
ZappySys automatically calculates and replaces these placeholders with actual date values when the query runs.
https://api.example.com/orders?start_date=2025-06-20&end_date=2025-06-27
Here are a few useful examples:
<<today,FUN_TO_DATE>>– current date<<now,FUN_TO_DATE>>– current date-time<<today-7d||yyyy-MM-dd,FUN_TO_DATE>>– 7 days ago<<now||yyyy-MM-dd HH:mm:ss.fff,FUN_TO_DATE>>– formatted date<<now,FUN_TO_DATETIME_UTC>>– get UTC date-time
ZappySys ODBC Driver Placeholder Functions
For more placeholders and functions, please refer to the following link:
https://zappysys.com/onlinehelp/odbc-powerpack/index.htm#page=odbc-format-static-placeholders.htm
Scenario 2: Passing Parameters using SQL Queries
Let’s say you’re working with a REST API that requires dynamic input, such as a city name for fetching weather data. The ZappySys ODBC Driver makes it incredibly easy to inject such variables into your API requests—whether you’re using SQL Server, stored procedures.
In this section, we’ll walk through two practical methods to dynamically pass a city name to fetch weather data
Use Case: Get Weather Data for a Dynamic City
Let’s say you want to retrieve weather data from OpenWeatherMap for any city the user specifies (e.g., London, Paris, Tokyo).
API Format:
https://api.openweathermap.org/data/2.5/weather?q=London&appid=12345
To make the q=London part dynamic, you have two main options:
Method 1: Dynamic SQL with EXEC and Linked Server
If you’re calling the ZappySys ODBC Driver through a linked server in SQL Server, you can inject the city name dynamically using SQL variables.
SQL to make a dynamic API call via Linked Server:
DECLARE @city nvarchar(4000)
DECLARE @sql nvarchar(4000)
SET @city = 'London'
SET @sql = '
SELECT * FROM "$"
WITH(
Src=''https://api.openweathermap.org/data/2.5/weather?q=' + @city + '&appid=12345''
)'
-- Replace [YOUR_LINKED_SERVER] with your actual linked server name
EXEC(@sql) AT [YOUR_LINKED_SERVER]
Note: Ensure that the linked server is configured using the ZappySys Data Gateway that connects to the JSON driver. For more detailed steps, refer to this article: JSON Connector for SQL Server
Method 2: Use a Custom Stored Procedure Inside the ODBC Driver
ZappySys supports Custom Stored Procedures defined directly inside the ODBC Driver. This is great for reusability and cleaner code.
Steps:
- Go to the Custom Objects tab in your JSON ODBC DSN settings.
- Add a new Stored Procedure with a dynamic parameter like
@city. - Use
<@param,FUN_TRIM>syntax to inject it.
Stored Procedure Definition:
CREATE PROCEDURE [usp_getweatherdata]
@city='London'
AS
SELECT * FROM "$"
WITH(
Src='https://api.openweathermap.org/data/2.5/weather?q=<@city,FUN_TRIM>&appid=12345'
)
SQL to Call the Procedure via Linked Server:
DECLARE @city nvarchar(4000)
DECLARE @sql nvarchar(4000)
SET @city = 'Frederick'
SET @sql='EXEC usp_getweatherdata @city='''+ @city +''''
EXEC(@sql) AT [YOUR_LINKED_SERVER]
This lets you pass the city name as a parameter while hiding the API logic behind a clean stored procedure.
Benefits of Using Stored Procedures
- Centralized logic for repeat use.
- Cleaner syntax for querying from SQL Server, Power BI, etc.
- Easier to secure and maintain.
Scenario 3: Dynamic Authorization Tokens
Many APIs — especially secure or enterprise-grade ones — require access tokens that expire after a short time. You cannot hardcode these tokens, as they expire frequently. Instead, you need a mechanism to dynamically generate and use fresh tokens in API requests.
Solution:
ZappySys offers a built-in feature called HTTP Dynamic Token Connection that solves this problem.
This feature allows you to:
- Automatically make a token request (e.g.,
GET/POST token) - Capture the
access_tokenfrom the response - Use it in a subsequent API call (e.g., via
Authorization: Bearer {{access_token}}header)
To first retrieve the token and then use it to make the actual API call, ZappySys offers an option called HTTP Dynamic Token Connection. This feature allows you to handle token-based authentication seamlessly.
For more details, please refer to the following article:
Conclusion
With the ZappySys ODBC Driver, passing dynamic parameters in API calls becomes seamless and SQL-driven. Whether you’re working with dates, tokens, or complex body payloads, this driver enables full control and flexibility through parameterization.