How to make HTTP API requests within SQL Server procedures

Data and Databases
1

I know how to consume web APIs in my .NET applications using standard HTTP clients:

HttpClient client = new HttpClient();
string response = await client.GetStringAsync("https://api.example.com/data?user=john");

But I’m wondering if there’s a way to directly invoke REST APIs from inside a SQL Server stored procedure. Is this even possible? If it can be done, what would be the best approach to capture the returned JSON or XML data and work with it in my database procedures? I need to integrate some external service calls directly into my database logic without having to go through a middle-tier application layer.

2

Yes, SQL Server can make HTTP requests in a few ways. If you’re using Azure SQL Database, sp_invoke_external_rest_endpoint is your best bet - it’s built for REST API calls. For on-premises setups, you can use CLR integration with a custom assembly, but you’ll need to enable CLR first and watch out for security issues. There’s also xp_cmdshell with PowerShell or curl, though that’s pretty risky security-wise. Once you get the JSON back, SQL Server’s JSON functions like JSON_VALUE and OPENJSON make parsing straightforward right in your stored procedures. Just heads up - HTTP calls from the database can hurt performance and tie your DB too closely to external services.

3

you could also use ole automation procedures with sp_OACreate and sp_OAMethod to create http objects, but it’s pretty outdated. you’ll need to enable ole automation first with sp_configure. honestly though, i’d skip doing api calls directly in sql - debugging becomes a total nightmare when stuff breaks.

4

Interesting challenge! What’s your plan if the external API goes down or gets slow? Your stored procs could hang forever without timeouts or retry logic built in. Also, what kind of data are you pulling that can’t wait for the application layer to handle it?