Everyone loves OpenAI these days as it can do some amazing things. Here, I asked it to create a picture of a cat in a data center:

(Go ahead and click the image to see a bigger version of this nightmare fuel)

Incredible!!!!

But how can we use OpenAI in real world applications and scenarios with Azure SQL Database?

Using OpenAI with the Azure SQL Database

Taking inspiration from Buck Woody’s blog “Secure your AI using SQL Server Machine Learning with Microsoft Azure OpenAI Services“, what can we do in Azure SQL Database if we don’t have access to SQL Server Machine Learning Services? We can use External REST Endpoint Invocation of course! So, let’s take a look at how to accomplish this step by step.

Setups

First, we need access to OpenAI in Azure. You can request access to this new service with this form here.

https://aka.ms/oai/access

Once you have access, creating an OpenAI service is very simple in the Azure Portal.

Click the Azure OpenAI symbol, provide it with some basic information like resource group, name, and location (use US East if you want to play around with DALL-E) and you are done.

Once the resource is created, click the Model Deployments option on the left side of the page, then click Manage Deployments to open OpenAI Studio in the browser.

Now in Azure AI Studio, click on the Deployments tab on the left if not already selected then click Create new deployment.

In the Deploy Model modal window, choose gpt-35-turbo. The gpt-35-turbo-16k option is for when you need very lengthy responses (perfect for those term papers) but does cost a bit more per request. For this example, we will not need the 16k option.

Next, name the model in the Deployment name field. Then click the blue Create button.

And that’s it, you now have your own personal ChatGPT prompt to talk to endlessly about live, the universe and who the Witches of Dathomir are.

Ask it a question

While in Azure AI Studio, click the Chat tab on the left side of the screen.

On the right of the page, we are going to use the Chat Session area to ask a question.

Use the text field on the bottom to ask a question, then click the paper airplane icon to submit it.

Within a few seconds, we get our answer.

While still on this page, on the top of the chat session tile click the View Code button.

In the Sample Code modal window, look at the bottom at the Endpoint and Key values. Copy these values for we will be using them later in the example.

OpenAI REST Endpoints

Using the External REST Endpoint Invocation stored procedure, we can now ask our OpenAI service a question from within an Azure SQL Database using data from a table. For this example, the AdventureWorks sample data has been loaded so that we have something to work with just as Mr. Buck Woody did in his blog post.

To start using the stored procedure, open a new query sheet if in Azure Data Studio, use SSMS, or use sqlcmd; whichever you are most comfortable with. The first example is going to ask a simple question as we did in the chat session page. Let’s build up the procedure from the start as well.

Starting to build our T-SQL code, we are going to declare and prefill some variables:

declare @url nvarchar(4000) = N'https://skynetbeta.openai.azure.com/openai/deployments/chattykathy/chat/completions?api-version=2023-07-01-preview';
declare @headers nvarchar(102) = N'{"api-key":"1001001sos1001001indistress"}'
declare @payload nvarchar(max) = N'{"messages":[{"role":"system","content":"Why is the sky blue?"}]}'
declare @ret int, @response nvarchar(max);

First, we start by declaring the URL or the Endpoint we want to use. Remember a bit back where we copied the Endpoint in the Sample Code modal? Use that URL here. Second, are the request headers. For this example, we are going to pass the api-key which allows us access to use the endpoint. Again, use the value you copied from the Sample Code modal key field. The payload variable will contain the chat prompt text we want to use to ask OpenAI a question. The last parameter we are declaring is the response variable. This will contain the answer gpt gives us in a JSON format.

Putting the declaration section together with the procedure call produces this T-SQL you can run against your Azure SQL Database:

declare @url nvarchar(4000) = N'https://skynetbeta.openai.azure.com/openai/deployments/chattykathy/chat/completions?api-version=2023-07-01-preview';
declare @headers nvarchar(102) = N'{"api-key":"1001001sos1001001indistress"}'
declare @payload nvarchar(max) = N'{"messages":[[{"role":"system","content":"You are an AI assistant that helps people find information."},
                                   {"role":"system","content":"Why is the sky blue?"}]}'
declare @ret int, @response nvarchar(max);
exec @ret = sp_invoke_external_rest_endpoint
    @url = @url,
    @method = 'POST',
    @headers = @headers,
    @payload = @payload,
    @timeout = 230,
    @response = @response output;
select @ret as ReturnCode, @response as Response;

"choices": [
    {
        "index": 0,
        "finish_reason": "stop",
        "message": {
            "role": "assistant",
            "content": "The sky appears blue because when the sun's light enters Earth's atmosphere,
                              it is scattered by the gases and particles in the air. This scattering causes the shorter
                              blue wavelengths of light to be dispersed more than the other colors in the spectrum.
                              This is known as Rayleigh scattering. As a result, when we look up at the sky during
                              the day, we see it as blue."
        },
        "content_filter_results": {
            "hate": {
                "filtered": false,
                "severity": "safe"
            },
            "self_harm": {
                "filtered": false,
                "severity": "safe"
            },
            "sexual": {
                "filtered": false,
                "severity": "safe"
            },
            "violence": {
                "filtered": false,
                "severity": "safe"
            }
        }
    }
],

SELECT Name  + ': ' + Description
  FROM SalesLT.vProductAndDescription
 WHERE ProductID = 980 and Culture = 'en';

Mountain-400-W Silver, 38: This bike delivers a high-level of performance on a budget.
It is responsive and maneuverable, and offers peace-of-mind when you decide to go off-road.

declare @adcopy VARCHAR(500);

set @adcopy =
(SELECT Name  + ': ' + Description
FROM SalesLT.vProductAndDescription
WHERE ProductID = 980 and Culture = 'en');

declare @payload nvarchar(max) = N'{"messages":[{"role":"system","content":"You are an AI assistant that helps people find information."},
                                   {"role":"user","content":"You are an experienced marketing expert. Generate 2000 letters of ad copy for '+(@adcopy)+'"}]}'

declare @adcopy VARCHAR(500);

set @adcopy =
(SELECT Name  + ': ' + Description
FROM SalesLT.vProductAndDescription
WHERE ProductID = 980 and Culture = 'en');

declare @url nvarchar(4000) = N'https://skynetbeta.openai.azure.com/openai/deployments/chattykathy/chat/completions?api-version=2023-07-01-preview';
declare @headers nvarchar(102) = N'{"api-key":"1001001sos1001001indistress"}'
declare @payload nvarchar(max) = N'{"messages":[{"role":"system","content":"You are an AI assistant that helps people find information."},
                                   {"role":"user","content":"You are an experienced marketing expert. Generate 2000 letters of ad copy for '+(@adcopy)+'"}]}'
declare @ret int, @response nvarchar(max);
exec @ret = sp_invoke_external_rest_endpoint
    @url = @url,
    @method = 'POST',
    @headers = @headers,
    @payload = @payload,
    @timeout = 230,
    @response = @response output;
select @ret as ReturnCode, @response as Response;

"message": {
    "role": "assistant",
    "content": "Dear Outdoor Enthusiasts,\n\nAre you searching for a high-performance bike that won't break the bank? 
                Look no further than the Mountain-400-W Silver, 38!\n\nThis top-of-the-line bike was designed to deliver
                a level of performance that's typically reserved for much pricier models. With responsive handling and
                unbeatable maneuverability, you'll be able to tackle even the toughest trails with ease.\n\nBut that's
                not all - the Mountain-400-W Silver, 38 also offers unparalleled peace of mind when you venture off-road.
                Its sturdy construction and reliable components mean that you can trust your bike to hold up to even the
                roughest terrain.\n\nSo why wait? Whether you're a seasoned pro or just starting out, the Mountain-400-W Silver,
                38 is the perfect choice for any mountain biker looking to take their skills to the next level. Order yours
                today and experience the ultimate in off-road performance!"
},

create database scoped credential [https://skynetbeta.openai.azure.com]
with identity = 'Managed Identity',
secret = '{"resourceid": "https://cognitiveservices.azure.com" }';

declare @url nvarchar(4000) = N'https://skynetbeta.openai.azure.com/openai/deployments/chattykathy/chat/completions?api-version=2023-07-01-preview';
declare @payload nvarchar(max) = N'{"messages":[{"role":"system","content":"You are an AI assistant that helps people find information."},
                                   {"role":"user","content":"Why is the sky blue?"}]}'
declare @ret int, @response nvarchar(max);

exec @ret = sp_invoke_external_rest_endpoint
    @url = @url,
    @method = 'POST',
    @payload = @payload,
    @timeout = 230,
    @credential = [https://skynetbeta.openai.azure.com],
    @response = @response output;
select @ret as ReturnCode, @response as Response;

Want to know more?