Advent of 2025, Day 11 – SQL Server 2025 – External REST endpoint invocation using LLM

USE Master;
GO

GRANT EXECUTE ANY EXTERNAL ENDPOINT TO dbo;
GO

USE db_06_api;
GO

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Very_Super_Strong_P@ssw0rd1!';
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_scoped_credentials WHERE name = 'https://tomaz-m821w85v-eastus2.cognitiveservices.azure.com')
BEGIN
  CREATE DATABASE SCOPED CREDENTIAL [https://tomaz-m821w85v-eastus2.cognitiveservices.azure.com]
  WITH IDENTITY = 'HTTPEndpointHeaders',
   SECRET   = '{"api-key":"your_api_key_from_Azure_AI_foundry_api_key"}';
END
GO

CREATE OR ALTER PROCEDURE dbo.sp_call_my_Foundry_LLM (
    @Prompt VARCHAR(MAX),
    @Fl_Debug BIT = 0
)
AS 
BEGIN

    -- Executa a API
    DECLARE
        @ret INT,
        @response NVARCHAR(MAX),
       
        @payload VARCHAR(MAX) = '{
      "messages": [
        {
          "role": "system",
          "content": [
            {
              "type": "text",
              "text": "I am reading this blog. ' + @Prompt + '"
            }
          ]
        }
      ],
      "temperature": 0.1,
      "top_p": 0.1,
      "max_tokens": 8000
    }'

    EXEC @ret = sys.sp_invoke_external_rest_endpoint 
	     @method = 'POST',
          @headers = '{"Content-Type":"application/json"}',
	     @url = N'https://tomaz-m821w85v-eastus2.cognitiveservices.azure.com/openai/deployments/gpt-4o/chat/completions?api-version=2025-01-01-preview',
         @payload = @payload,
         --@credential = [<OPENAI_URL>],
         @credential = [https://tomaz-m821w85v-eastus2.cognitiveservices.azure.com],
	     @response = @response OUTPUT

    -- PRINT @response


    DECLARE @API_RETURN VARCHAR(MAX)
    SET @API_RETURN = JSON_VALUE(@response, '$.result.choices[0].message.content')
    SET @API_RETURN = REPLACE(REPLACE(@API_RETURN, '```sql', ''), '```', '')

    PRINT @API_RETURN

    IF (LEN(TRIM(@API_RETURN)) > 0 AND @Fl_Debug = 0)
        EXEC(@API_RETURN)

END

-- EXECUTE API
EXEC dbo.sp_call_my_Foundry_LLM 'Where can I buy book on SQL Server 2025?'