AI, Artificial Intelligence (AI), Databases, DP-800, Microsoft Certification

Create a prompt by using the sp_invoke_external_rest_endpoint stored procedure (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement retrieval-augmented generation (RAG)
      --> Identify use cases for RAG

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

As SQL databases increasingly integrate with AI services, developers can call external REST APIs directly from Transact-SQL (T-SQL). One important capability is the sp_invoke_external_rest_endpoint system stored procedure, which enables SQL code to securely invoke REST endpoints, including AI services such as Azure AI Foundry models, Azure OpenAI Service, and other HTTP-based APIs.

For the DP-800 exam, you should understand how to use this stored procedure to build prompts, send requests to AI models, process responses, and incorporate Retrieval-Augmented Generation (RAG) workflows into SQL applications.

What Is sp_invoke_external_rest_endpoint?

sp_invoke_external_rest_endpoint is a system stored procedure that enables T-SQL code to invoke external REST APIs directly from supported SQL platforms, such as Azure SQL Database and SQL Server 2025 (where supported and configured).

Instead of requiring an application layer to call an external AI service, the database itself can send an HTTPS request and receive the response.

Typical workflow:

T-SQL
    │
    ▼
sp_invoke_external_rest_endpoint
    │
    ▼
REST API
    │
    ▼
AI Model
    │
    ▼
JSON Response
    │
    ▼
SQL Processing

This capability allows SQL applications to integrate directly with AI-powered services while keeping business logic close to the data.

Why Use sp_invoke_external_rest_endpoint?

Many AI services expose REST APIs.

Examples include:

  • Azure OpenAI
  • Azure AI Foundry models
  • Azure AI Language
  • Azure AI Translator
  • Azure AI Vision
  • Custom REST APIs
  • Internal enterprise AI services

Using sp_invoke_external_rest_endpoint enables SQL developers to:

  • Generate AI responses
  • Summarize database content
  • Perform sentiment analysis
  • Translate text
  • Classify documents
  • Invoke Retrieval-Augmented Generation (RAG) workflows
  • Call custom enterprise AI services

Role in Retrieval-Augmented Generation (RAG)

In a RAG solution, the database typically performs several tasks:

  1. Retrieve relevant documents.
  2. Build the prompt.
  3. Call the LLM.
  4. Return the grounded response.

Example workflow:

User Question
      │
      ▼
Vector Search
      │
      ▼
Retrieve Context
      │
      ▼
Build Prompt
      │
      ▼
sp_invoke_external_rest_endpoint
      │
      ▼
Large Language Model
      │
      ▼
Grounded Answer

The stored procedure serves as the bridge between SQL and the external AI model.

Components of an AI Request

A typical AI request contains several elements.

Endpoint URL

The REST endpoint specifies where the request is sent.

Examples include:

  • Azure OpenAI endpoint
  • Azure AI Foundry endpoint
  • Internal REST API

HTTP Method

Most AI inference requests use:

POST

because prompt data is sent in the request body.

HTTP Headers

Headers commonly include:

  • Authorization
  • Content-Type
  • API version (when required)
  • Subscription key (for applicable services)

Example:

Content-Type: application/json
Authorization: Bearer <token>

Authentication methods vary by service and may use Microsoft Entra ID (formerly Azure Active Directory), managed identities, or API keys.

JSON Request Body

The request body contains:

  • Prompt
  • System instructions
  • User input
  • Generation parameters

Example:

{
  "messages": [
    {
      "role": "system",
      "content": "You are a SQL assistant."
    },
    {
      "role": "user",
      "content": "Explain clustered indexes."
    }
  ]
}

The exact JSON schema depends on the AI service being called.

Creating Effective Prompts

Prompt engineering significantly affects AI output quality.

A good prompt should include:

  • Clear instructions
  • Business context
  • Retrieved documents (for RAG)
  • User question
  • Expected output format

Example Prompt Structure

System:
You are an expert SQL assistant.
Context:
<Document retrieved from vector search>
Question:
How do clustered indexes improve performance?
Instructions:
Answer only using the supplied context.

This structure helps reduce hallucinations and produces grounded responses.

Building Prompts in SQL

Developers often assemble prompts dynamically using T-SQL variables.

Conceptually:

DECLARE @Context NVARCHAR(MAX);
DECLARE @Question NVARCHAR(MAX);
SET @Context =
'Clustered indexes store table rows in key order...';
SET @Question =
'Explain clustered indexes.';

The prompt can then be incorporated into the JSON request body before invoking the external endpoint.

Note: The exact JSON construction depends on the target AI service’s REST API.

Example Workflow

A simplified workflow is:

Retrieve Documents
↓
Build Prompt
↓
Create JSON Payload
↓
Call REST Endpoint
↓
Receive JSON Response
↓
Extract Generated Answer

Conceptual Example

The following simplified example illustrates the overall flow. It is not intended to represent every required parameter or authentication option.

EXEC sp_invoke_external_rest_endpoint
    @method = 'POST',
    @url = 'https://<ai-endpoint>',
    @headers = '{"Content-Type":"application/json"}',
    @payload = '{"messages":[...]}';

The supported parameters, authentication methods, and payload format depend on the SQL platform and the REST API being invoked.

Using Retrieved Context in RAG

Suppose vector search returns:

Document:

Clustered indexes physically organize rows according to the index key.

User asks:

Why are clustered indexes faster?

Prompt:

Use only the following information:
Clustered indexes physically organize rows according to the index key.
Question:
Why are clustered indexes faster?

This grounded prompt improves response accuracy.

Processing the Response

Most AI services return JSON.

Example (simplified):

{
  "choices": [
    {
      "message": {
        "content": "Clustered indexes improve performance because..."
      }
    }
  ]
}

SQL applications can use JSON functions such as:

  • OPENJSON
  • JSON_VALUE
  • JSON_QUERY

to extract values from the response.

Example:

SELECT JSON_VALUE(@Response,
'$.choices[0].message.content');

Authentication Considerations

REST endpoints must be secured.

Depending on the service, authentication may use:

  • Microsoft Entra ID
  • Managed Identity
  • API Keys
  • OAuth access tokens

Developers should avoid embedding secrets directly in T-SQL code.

Instead, use secure credential management mechanisms supported by the platform.

Error Handling

Common failures include:

Authentication Errors

Examples:

  • Invalid token
  • Expired credentials
  • Missing permissions

Network Errors

Examples:

  • Endpoint unavailable
  • Timeout
  • DNS failures

Invalid Request

Examples:

  • Incorrect JSON
  • Unsupported parameter
  • Missing required fields

Rate Limiting

Many AI services enforce request limits.

Applications should be designed to handle HTTP responses such as:

  • 429 Too Many Requests

using retry logic with exponential backoff where appropriate.

Performance Considerations

Calling external AI services introduces additional latency.

Factors include:

  • Network communication
  • AI inference time
  • Prompt size
  • Response size
  • Concurrent requests

Large prompts increase:

  • Token usage
  • Response time
  • Cost

Developers should include only the most relevant retrieved context.

Security Best Practices

When invoking external AI services:

  • Use HTTPS endpoints.
  • Authenticate securely using supported identity mechanisms.
  • Protect API credentials.
  • Validate user input before constructing prompts.
  • Avoid exposing confidential information unnecessarily.
  • Apply least-privilege access.
  • Monitor outbound API usage.
  • Log failures for troubleshooting while avoiding logging sensitive prompt content.

Best Practices for Prompt Design

  • Provide clear system instructions.
  • Include only relevant retrieved context.
  • Tell the model to answer using the supplied context.
  • Specify the desired output format.
  • Keep prompts concise to reduce latency and token consumption.
  • Remove duplicate or irrelevant information.
  • Test prompts using realistic business questions.
  • Evaluate responses for accuracy and consistency.

Common RAG Prompt Pattern

A common prompt template includes:

System
↓
Instructions
↓
Retrieved Context
↓
User Question
↓
Expected Response Format

This structure helps produce consistent, grounded responses.

DP-800 Exam Tips

Remember these key points for the exam:

  • sp_invoke_external_rest_endpoint enables T-SQL code to call external REST APIs directly.
  • In RAG solutions, the stored procedure is commonly used after retrieving relevant documents and constructing a grounded prompt.
  • AI requests typically use the HTTP POST method with a JSON payload.
  • Prompt quality directly influences response quality.
  • Include retrieved context to reduce hallucinations.
  • Responses from AI services are typically returned as JSON and can be parsed using SQL JSON functions.
  • Secure authentication is essential; avoid hard-coding credentials.
  • Minimize prompt size to improve performance and reduce token costs.

Practice Exam Questions

Question 1

A developer wants to call an Azure AI model directly from T-SQL without writing application code.

Which SQL capability enables this functionality?

A. sp_execute_external_script

B. OPENROWSET

C. sp_invoke_external_rest_endpoint

D. BULK INSERT

Answer: C

Explanation:
sp_invoke_external_rest_endpoint enables supported SQL platforms to invoke external REST APIs directly from T-SQL, making it suitable for integrating AI services.

Question 2

In a Retrieval-Augmented Generation (RAG) solution, when is sp_invoke_external_rest_endpoint typically called?

A. Before documents are retrieved.

B. After relevant context has been retrieved and incorporated into the prompt.

C. Before embeddings are generated.

D. Before the vector index is created.

Answer: B

Explanation:
In a typical RAG workflow, relevant documents are first retrieved using vector or hybrid search. The retrieved context is then included in the prompt before calling the LLM through the REST endpoint.

Question 3

Which HTTP method is most commonly used when invoking an AI chat completion REST endpoint?

A. GET

B. DELETE

C. PUT

D. POST

Answer: D

Explanation:
AI inference requests generally send prompts and parameters within the request body, making POST the standard HTTP method.

Question 4

What is the primary benefit of including retrieved documents in the prompt sent to an AI model?

A. It permanently trains the language model.

B. It reduces network latency.

C. It grounds the response using relevant information.

D. It compresses the prompt.

Answer: C

Explanation:
Including retrieved context allows the model to generate responses based on trusted information, improving accuracy and reducing hallucinations.

Question 5

Which SQL functionality is commonly used to extract generated text from a JSON response returned by an AI service?

A. JSON_VALUE

B. MERGE

C. PIVOT

D. ROW_NUMBER

Answer: A

Explanation:
Functions such as JSON_VALUE, JSON_QUERY, and OPENJSON enable SQL developers to parse JSON responses returned by REST APIs.

Question 6

A developer is designing prompts for an AI-powered SQL assistant.

Which prompt design practice generally produces the most reliable responses?

A. Include unrelated historical data to provide additional context.

B. Keep prompts vague so the model has more flexibility.

C. Provide clear instructions and include only relevant retrieved context.

D. Omit the user’s question whenever possible.

Answer: C

Explanation:
Clear instructions and focused, relevant context help the model generate accurate, grounded, and consistent responses.

Question 7

Which authentication approach is recommended when calling secured AI REST endpoints from SQL?

A. Store API keys directly in every stored procedure.

B. Use supported secure authentication mechanisms such as Microsoft Entra ID or managed identities where available.

C. Disable authentication during development and production.

D. Send credentials as query-string parameters.

Answer: B

Explanation:
Secure authentication methods reduce the risk of credential exposure and align with security best practices for accessing external services.

Question 8

What is a common consequence of including excessive retrieved content in a prompt?

A. Lower token usage.

B. Faster inference times.

C. Reduced storage requirements.

D. Increased latency and higher token consumption.

Answer: D

Explanation:
Longer prompts require more tokens to process, increasing inference time, cost, and the likelihood of exceeding the model’s context window.

Question 9

A database application receives an HTTP 429 response from an AI REST endpoint.

What does this response typically indicate?

A. The JSON response is malformed.

B. Authentication failed.

C. The request exceeded the service’s rate limit.

D. The endpoint only accepts GET requests.

Answer: C

Explanation:
HTTP 429 (“Too Many Requests”) indicates that the client has exceeded the allowed request rate. Applications should implement appropriate retry strategies.

Question 10

Which sequence best represents a typical RAG workflow implemented from SQL?

A. Generate response → Retrieve documents → Build prompt → Parse JSON

B. Retrieve documents → Build prompt → Invoke sp_invoke_external_rest_endpoint → Parse the JSON response

C. Create vector index → Generate embeddings → Train the LLM

D. Build prompt → Delete vector index → Generate embeddings

Answer: B

Explanation:
A typical SQL-based RAG workflow retrieves relevant documents, constructs a grounded prompt, invokes the external AI service using sp_invoke_external_rest_endpoint, and then parses the returned JSON response for use by the application.

Go to the DP-800 Exam Prep Hub main page

Leave a comment