sp_invoke_external_rest_endpoint is a game changer!

November 17, 2022

This internal procedure is new in Azure SQL DB in 2022. I think it presents a significant change to the way we do things in the world of SQL, and makes some other tools a whole lot more useful as well.

sp_invoke_external_rest_endpoint lets me send data to a REST API from within a stored procedure. Invoking an HTTP REST endpoint – as simple as that. And while I know you’re probably thinking, “But I can send data to a REST API from anywhere – why do I need to do it from within a stored procedure?”, I want to describe a few scenarios to you.

If your database is just storing data that is worked out elsewhere, such that your processes do all the work, figure out all the values, and simply insert the values into a table at the end, then any REST APIs that you need to call are going to be coming from those processes. No advantage here to call the REST API from within a stored procedure.

But quite often, a lot of the work is done inside a stored procedure, and then results are passed back to somewhere else so that a REST API can be called.

Well now you don’t need to pass those results back to some other process just to get a REST API called.

But even more than that, you don’t need to wait for your stored proc processes to finish to call a REST API either.

I’m a consultant and see plenty of stored procedures that do a lot of work. Not just one query – lots of queries. Or they call several other stored procedures. Sometimes they’re hefty transformations as part of a warehouse load. Before now, you’d need to have some other process call each part of the stored procedure if you needed to call a REST API in between. Hopefully you just didn’t need to.

But now you can.

Maybe you want to pass a bunch of data to a Machine Learning function so that your procedure can continue. Now just do it inside the stored procedure.

Maybe you wanted to kick off a separate process, like a Logic Apps workflow. Now just do it inside the stored procedure.

But the scenario I’m going to look at is simply monitoring the progress of your stuff. Because a lot of people use Power BI these days, and Power BI can easily present a REST API for pushing data into.

So I’m going to start by heading to powerbi.com and creating a streaming dataset that uses the API. I’m going to set up a few values to play with:

The kind of values I want to use here are things to show me the status of how things are going. I’m figure that showing some text, a date, and a value might be useful. I’m also going to include a MinVal and a MaxVal, because I like the Gauge visual for what I’m going to do.

Now over in Azure SQL DB, I’m going to create a stored procedure that pushes data to this set. It’s going to call sp_invoke_external_rest_endpoint of course, sending in a JSON payload, which I create using FOR JSON PATH – because FOR JSON PATH means I don’t need to think about formatting my JSON, worrying about whether it needs the square brackets on the outside (it does), and all that. I’m going to pick up the response too. For my particular needs, I’m going to fix MinVal and MaxVal as 0 and 100, but I still want them in this dataset, because it’s how the visual works.

(Naturally the URL here is not the real one. Go make your own dataset and use the URL from that.)

create or alter proc dbo.PushToPBI (@Text varchar(1000) = null, @Value int = null, @DateTime datetime = null, @returnval int = null output)
as
begin
declare @payload nvarchar(max) = (
select @Text as TextToShow, @Value as NumberToShow, @DateTime as DateToShow, 0 as MinVal, 100 as MaxVal
for json path
);
declare @response nvarchar(max);
EXEC @returnval = sp_invoke_external_rest_endpoint 
@url = N'https://api.powerbi.com/beta/00029a0b-28a4-4b60-aed5-b8b504506e77/datasets/e31a3dc5-553d-4ac7-b678-de9fec57f799/rows?key=%2FjVJm1G5FvTU8q3M5so4QLxhWsIb0fSXB3eN23526OKy0yz%2BxXx3MHxJ2bp2Q%2BA2z7gWCKGpH7c6r6mLIaUdlA%3D%3D'
,@payload = @payload
,@method = 'POST'
,@response = @response output;
; 
select v.*
from openjson(@response) r
cross apply openjson(r.value) v
end

create or alter proc dbo.PushToPBI (@Text varchar(1000) = null, @Value int = null, @DateTime datetime = null, @returnval int = null output)

begin

declare @payload nvarchar(max) = (

select @Text as TextToShow, @Value as NumberToShow, @DateTime as DateToShow, 0 as MinVal, 100 as MaxVal

for json path

);

declare @response nvarchar(max);

EXEC @returnval = sp_invoke_external_rest_endpoint

@url = N'https://api.powerbi.com/beta/00029a0b-28a4-4b60-aed5-b8b504506e77/datasets/e31a3dc5-553d-4ac7-b678-de9fec57f799/rows?key=%2FjVJm1G5FvTU8q3M5so4QLxhWsIb0fSXB3eN23526OKy0yz%2BxXx3MHxJ2bp2Q%2BA2z7gWCKGpH7c6r6mLIaUdlA%3D%3D'

,@payload = @payload

,@method = 'POST'

,@response = @response output;

;

select v.*

from openjson(@response) r

cross apply openjson(r.value) v

end

Now I’m going to simulate my long running procedure. I’m sure you can picture it, but I’m going to replace where all the work is done with a couple of WAITFOR commands. The new thing is that I’m going to call my PushToPBI procedure in between. I’m going to push the value 0 at the start, 50 half way through, and 100 at the end. Obviously if I had more steps, I could do send more values. I’m sure you get my drift.

create or alter proc dbo.LongRunningProcedure
as
begin
declare @Now datetime;
set @Now = getdate()
exec dbo.PushToPBI @Value=0, @DateTime = @Now, @Text = 'Just started';
waitfor delay '00:00:05'
set @Now = getdate()
exec dbo.PushToPBI @Value=50, @DateTime = @Now, @Text = 'We''re livin'' on a prayer!';
waitfor delay '00:00:05'
set @Now = getdate()
exec dbo.PushToPBI @Value=100, @DateTime = @Now, @Text = 'And we''re done';
end

create or alter proc dbo.LongRunningProcedure

begin

declare @Now datetime;

set @Now = getdate()

exec dbo.PushToPBI @Value=0, @DateTime = @Now, @Text = 'Just started';

waitfor delay '00:00:05'

set @Now = getdate()

exec dbo.PushToPBI @Value=50, @DateTime = @Now, @Text = 'We''re livin'' on a prayer!';

waitfor delay '00:00:05'

set @Now = getdate()

exec dbo.PushToPBI @Value=100, @DateTime = @Now, @Text = 'And we''re done';

end

Now back in Power BI, I’m going to create a new dashboard and add a tile. I tell the tile to use Custom Streaming Data, and when I use Gauge, I can populate it with the Number, MinVal, and MaxVal from my new dataset.

And hey presto, I have a progress bar for my long-running process, which I can have showing on my screen, on my mobile, whatever, and it will refresh without my having to do anything more.

For a while it’s been pretty easy to push data like this into Power BI to monitor how things are going in between the big stored proc calls. And with a little clever querying of dynamic management views in SQL I can figure out which query in a long batch is running from PowerShell (polling it, not in response to an event), and push data into Power BI based on the results of that query. But being able to push data to an HTTP endpoint part way through execution of a stored procedure means I can drop these calls in at all kinds of convenient spots in my code (bearing in mind it would add to the amount of time the proc takes to run).

And like I said – it’s not just about monitoring. Really this just gives me a proper amount of control over the processes, without forcing me to leave the SQL proc to do it.

@rob_farley@twitter