SQL Server 2025 REST API Example

Problem

You have an online store that uses SQL Server. One of the things that is often required is to determine shipping costs. In this article, we will look at how you can use an API in SQL Server to pass a few parameter values from SQL Server and return data from the API.

Solution

Microsoft has introduced native REST API endpoints with SQL Server 2025; this enables you to interact with external APIs directly on your SQL Server.

There is a stored procedure named sp_invoke_external_rest_endpoint that invokes an HTTPS REST endpoint provided as an input argument to the procedure. We will use this procedure and others to automatically calculate rates and delivery ETA then store the data for consumption for your application-level logic.

Create Database and Tables

Assuming you have your SQL Server 2025 installed and running, open SSMS and connect to your database engine.

Then, click on the New Query button on the SSMS home ribbon and add the following code to add a create a database and table to simulate a sales orders table:

--MSSQLTIPS CODE
 
CREATE DATABASE SalesDB;
GO
USE SalesDB;
GO
 
CREATE TABLE dbo.SalesOrder 
( 
   OrderID INT IDENTITY PRIMARY KEY, 
   CustomerID INT NOT NULL, 
   FromPostalCode NVARCHAR(20) NOT NULL, 
   ToPostalCode NVARCHAR(20) NOT NULL, 
   WeightKg DECIMAL(10,2) NOT NULL, 
   ChosenService NVARCHAR(50) NOT NULL, -- e.g. STANDARD / EXPRESS 
   ShippingRate DECIMAL(10,2) NOT NULL, -- Best rate chosen 
   ETA_Days INT, 
   CreatedAt DATETIME NOT NULL DEFAULT SYSDATETIME(),
   CONSTRAINT FK_SalesOrder_Customer FOREIGN KEY (CustomerID) REFERENCES dbo.Customer(CustomerID)
);

Then, create another table to store the history of the shipping prices for future analysis:

CREATE TABLE dbo.ShippingRateHistory
(
   RateID INT IDENTITY PRIMARY KEY,
   OrderID INT NULL,                        -- Only linked if order is finalized
   RequestJson NVARCHAR(MAX) NOT NULL,
   ResponseJson NVARCHAR(MAX) NOT NULL,
   LoggedAt DATETIME NOT NULL DEFAULT SYSDATETIME()
);

If delivery for the all shipments will be from the same warehouse address, you can set this address value in your database:

CREATE TABLE dbo.WarehouseConfig
(
   WarehouseID INT IDENTITY PRIMARY KEY,
   WarehouseName NVARCHAR(100) NOT NULL,
   PostalCode NVARCHAR(20) NOT NULL,
   CountryCode CHAR(2) NOT NULL DEFAULT 'ZA',
   IsDefault BIT NOT NULL DEFAULT 1
);
 
INSERT INTO dbo.WarehouseConfig (WarehouseName, PostalCode)
VALUES ('Main Warehouse', '7441');

Enable RESTful API on SQL Server

You can check if your RESTful API feature is enabled by running this query:

SELECT name, value, value_in_use, description 
FROM sys.configurations 
WHERE name LIKE '%REST%';

This query will give you results of ‘external rest endpoint enabled’ being 0 if disabled or 1 if enabled.

If it’s not enabled, run the following query to enable it:

EXEC sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;

This should give you a result like this:

Configuration option 'external rest endpoint enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

Then, confirm its active by running this query:

EXEC sp_configure 'external rest endpoint enabled';

You should get the following results if it is running:

name	minimum	maximum	config_value	run_value
external rest endpoint enabled	0	1	1	1

Calling sp_invoke_external_rest_endpoint

The procedure accepts the following arguments:

EXECUTE @returnValue = sp_invoke_external_rest_endpoint 
[ @url = ] N'url' 
[ , [ @payload = ] N'request_payload' ] 
[ , [ @headers = ] N'http_headers_as_json_array' ] 
[ , [ @method = ] 'GET' | 'POST' | 'PUT' | 'PATCH' | 'DELETE' | 'HEAD' ] 
[ , [ @timeout = ] seconds ]
[ , [ @credential = ] credential ] 
[ , @response OUTPUT ] 
[ , [ @retry_count = ] 
# of retries if there are errors ]

Shipping Stored Procedure

This article uses the shiplogic api. We will create a SQL Server stored procedure that will send a request to the API and store the response as the OUTPUT variable.

First, you must create a database Master Key and then a database scoped credential to store your API key or any secret:

USE SalesDB;
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword!2025';
GO

And for the scoped credential:

CREATE DATABASE SCOPED CREDENTIAL ShipLogicAuth
WITH IDENTITY = 'HTTPEndpointHeaders',
SECRET = '{"Authorization":"Bearer <YourKey>"}';

Then, create the procedure to get the shipping rates for your application. As you can see in the code a bunch of items are hard coded but you can extend this further to pass more values to the stored procedure and then to the API.

USE [SalesDB]
GO 
 
CREATE OR ALTER PROCEDURE [dbo].[GetShipLogicRate] ( 
   @ToPostal NVARCHAR(20), 
   @WeightKg DECIMAL(10,2), 
   @ResponseJson NVARCHAR(MAX) OUTPUT ) 
AS 
BEGIN 
SET NOCOUNT ON;
 
-- Warehouse info
DECLARE @FromPostal NVARCHAR(20) = (
    SELECT TOP 1 PostalCode
    FROM dbo.WarehouseConfig
    WHERE IsDefault = 1
);
DECLARE @url NVARCHAR(4000) = 'https://api.shiplogic.com/v2/rates';
-- Headers
-- please make sure that there is not spaces on your headers
DECLARE @headers NVARCHAR(MAX) = N'{"Content-Type":"application/json","User-Agent":"SQLServer"}';
-- Payload
DECLARE @Payload NVARCHAR(MAX) = 
N'{
    "collection_address": {
        "type": "business",
        "company": "Shiplogic",
        "street_address": "194 Bancor Avenue",
        "local_area": "Menlyn",
        "city": "Pretoria",
        "zone": "Gauteng",
        "country": "ZA",
        "code": "' + @FromPostal + '"
    },
    "delivery_address": {
        "type": "residential",
        "company": "",
        "street_address": "10 Midas Avenue",
        "local_area": "",
        "city": "",
        "zone": "",
        "country": "ZA",
        "code": "' + @ToPostal + '"
    },
    "parcels": [
        {
            "submitted_length_cm": 40,
            "submitted_width_cm": 30,
            "submitted_height_cm": 10,
            "submitted_weight_kg": ' + CAST(@WeightKg AS NVARCHAR(10)) + '
        }
    ],
    "declared_value": 1500,
    "collection_min_date": "' + CONVERT(NVARCHAR(10), GETDATE(), 23) + '",
    "delivery_min_date": "' + CONVERT(NVARCHAR(10), GETDATE(), 23) + '"
}';

BEGIN TRY
        EXEC sp_invoke_external_rest_endpoint
            @url = @url,
            @method = 'POST',
            @headers = @headers,
            @payload = @payload,
            @credential = ShipLogicAuth,
            @response = @ResponseJson OUTPUT;
END TRY
BEGIN CATCH
        SELECT 
            ERROR_MESSAGE() AS ErrorMessage,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_LINE() AS ErrorLine,
            ERROR_PROCEDURE() AS ErrorProc;
END CATCH;
END;

Then test this stored procedure by running this code:

USE SalesDB;
GO
 
DECLARE @response NVARCHAR(MAX);
 
EXEC dbo.GetShipLogicRate @ToPostal = '2196', @WeightKg = 3.0, @ResponseJson = @response OUTPUT;
 
SELECT @response AS RawResponseJson;
 
SELECT TOP 1 CAST(
   JSON_VALUE(rate.value, '$.rate') AS DECIMAL(10,2)) AS CheapestRate, 
   JSON_VALUE(rate.value, '$.service_level.code') AS ServiceCode,  
   JSON_VALUE(rate.value, '$.service_level.name') AS ServiceName,  
   JSON_VALUE(rate.value, '$.service_level.description') AS ServiceDescription  
FROM OPENJSON(@response, '$.result.rates') AS rate 
ORDER BY CAST(JSON_VALUE(rate.value, '$.rate') AS DECIMAL(10,2));

Sales Order Stored Procedure to Store Rates Automatically

After you have successfully ran the procedure above, you can now use it to automate the process of getting the shipping rates and saving them in your database table using the following SQL Server stored procedure:

-- MSSQLTIPS CODE
USE SalesDB; 
GO
CREATE OR ALTER PROCEDURE [dbo].[CreateSalesOrder] ( 
   @CustomerID INT , 
   @ToPostal NVARCHAR(20) , 
   @WeightKg DECIMAL(10,2) ) 
AS 
BEGIN 
 
SET NOCOUNT ON;
 
DECLARE @json NVARCHAR(MAX);
 
-- Get default warehouse origin postal
DECLARE @FromPostal NVARCHAR(20) = (
    SELECT TOP 1 PostalCode 
    FROM dbo.WarehouseConfig 
    WHERE IsDefault = 1
);
 
-- Call ShipLogic Rate Procedure
EXEC dbo.GetShipLogicRate  
      @ToPostal = @ToPostal,
      @WeightKg = @WeightKg,
      @ResponseJson = @json OUTPUT; 
 
;WITH RateOptions AS (
    SELECT
        JSON_VALUE(value, '$.service_level.name') AS ServiceName,
        CAST(JSON_VALUE(value, '$.rate') AS DECIMAL(10,2)) AS Price,
        -- ETA calculation using delivery_date_to datetime
        DATEDIFF(DAY, CAST(GETDATE() AS DATE), 
        TRY_CONVERT(DATE, JSON_VALUE(value, '$.service_level.delivery_date_to')) ) AS ETA
    FROM OPENJSON(@json, '$.result.rates')
)
SELECT TOP 1 *
INTO #BestRate
FROM RateOptions
WHERE Price IS NOT NULL  -- avoid nulls breaking ordering
ORDER BY Price ASC;
-- Insert into SalesOrder
INSERT INTO dbo.SalesOrder (CustomerID, FromPostalCode, ToPostalCode, WeightKg, ChosenService, ShippingRate, ETA_Days)
SELECT
    @CustomerID,
    @FromPostal,
    @ToPostal,
    @WeightKg,
    ServiceName,
    Price,
    ETA
FROM #BestRate;
SELECT 
    SCOPE_IDENTITY() AS OrderID,
    ServiceName,
    Price,
    ETA
FROM #BestRate;
END;

Run an example call with the following script:

EXEC dbo.CreateSalesOrder 
      @CustomerID = 1,
      @ToPostal = '2196',
      @WeightKg = 2.5;

After running this, you should see a reply like this:

Next Steps

Check out these related articles:

Levi Masonde

Levi Masonde is a developer passionate about analyzing large datasets and creating useful information from these data. He is proficient in Python, ReactJS, and Power Platform applications. He is responsible for creating applications and managing databases as well as a lifetime student of programming and enjoys learning new technologies and how to utilize and share what he learns.

MSSQLTips Awards:
Trendsetter (25+ tips) – 2025 | Author of the Year Contender – 2023 | Rookie of the Year Contender – 2022

2 Comments

Matthew Spieth
January 5, 2026 / 7:10 pm Reply
Just make a generic Customer table, I guess? FOREIGN KEY (CustomerID) REFERENCES dbo.Customer(CustomerID)
- Harvey
  January 7, 2026 / 11:35 am Reply
  CREATE TABLE dbo.Customer (
  CustomerID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Email VARCHAR(100),
  Phone VARCHAR(20),
  Address VARCHAR(50)
  — Foreign key constraint would be added here
  );