This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Implement programmability objects
      --> Create stored procedures

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

Stored procedures are one of the most powerful programmability objects available in SQL Server and Azure SQL Database. A stored procedure is a precompiled collection of one or more Transact-SQL (T-SQL) statements that perform a specific task. Stored procedures allow developers to encapsulate business logic, automate repetitive database operations, improve application security, and optimize performance.

Stored procedures are widely used in enterprise applications for Create, Read, Update, Delete (CRUD) operations, reporting, data validation, data processing, ETL workflows, auditing, and integrating applications with databases. They are also valuable in AI-enabled database solutions where they can orchestrate data preparation, invoke AI-related operations, and manage workflows that interact with vector data, embeddings, and Retrieval-Augmented Generation (RAG) pipelines.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• What stored procedures are
• How to create, alter, execute, and delete stored procedures
• Input and output parameters
• Returning result sets and status codes
• Error handling
• Transactions
• Dynamic SQL
• Temporary objects
• Security considerations
• Performance optimization
• Best practices

Understanding stored procedures is essential because they are one of the primary methods for implementing reusable business logic in SQL Server.

What Is a Stored Procedure?

A stored procedure is a named database object that contains one or more SQL statements that execute as a unit.

Unlike functions, stored procedures:

• Can return zero, one, or multiple result sets
• Can modify database data
• Can execute DDL and DML statements
• Can contain transactions
• Can execute dynamic SQL
• Can include error handling
• Can call other stored procedures

Stored procedures are stored within the database and executed on demand.

Benefits of Stored Procedures

Stored procedures provide numerous advantages:

• Encapsulate business logic
• Reduce duplicate SQL code
• Improve security
• Simplify application development
• Improve maintainability
• Reduce network traffic
• Support transaction management
• Improve performance through execution plan reuse
• Simplify administrative operations

Creating a Stored Procedure

Basic syntax:

CREATE PROCEDURE dbo.uspGetCustomers
AS
BEGIN
    SELECT
        CustomerID,
        CustomerName,
        City
    FROM Sales.Customers;
END;

Execute the procedure:

EXEC dbo.uspGetCustomers;

or

EXECUTE dbo.uspGetCustomers;

Creating a Stored Procedure with Parameters

Most stored procedures accept one or more parameters.

Example:

CREATE PROCEDURE dbo.uspGetCustomerOrders
(
    @CustomerID INT
)
AS
BEGIN
    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID;
END;

Execute:

EXEC dbo.uspGetCustomerOrders
    @CustomerID = 100;

Parameters make procedures reusable across many scenarios.

Using Multiple Parameters

Example:

CREATE PROCEDURE dbo.uspOrdersByDate
(
    @StartDate DATE,
    @EndDate DATE
)
AS
BEGIN
    SELECT *
    FROM Sales.Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate;
END;

Optional Parameters

Parameters may have default values.

Example:

CREATE PROCEDURE dbo.uspGetOrders
(
    @Status VARCHAR(20) = 'Open'
)
AS
BEGIN
    SELECT *
    FROM Sales.Orders
    WHERE Status = @Status;
END;

Now the procedure can be executed with or without specifying the parameter.

Output Parameters

Stored procedures can return values through output parameters.

Example:

CREATE PROCEDURE dbo.uspGetOrderCount
(
    @CustomerID INT,
    @OrderCount INT OUTPUT
)
AS
BEGIN
    SELECT
        @OrderCount = COUNT(*)
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID;
END;

Execution:

DECLARE @Count INT;
EXEC dbo.uspGetOrderCount
    @CustomerID = 100,
    @OrderCount = @Count OUTPUT;
SELECT @Count;

Returning Status Codes

Stored procedures may return an integer status code.

Example:

CREATE PROCEDURE dbo.uspExample
AS
BEGIN
    RETURN 0;
END;

Execution:

DECLARE @ReturnCode INT;
EXEC @ReturnCode = dbo.uspExample;
SELECT @ReturnCode;

A return code is commonly used to indicate success or failure.

Returning Result Sets

Stored procedures frequently return result sets.

Example:

CREATE PROCEDURE dbo.uspTopCustomers
AS
BEGIN
    SELECT TOP (10)
        CustomerName,
        TotalSales
    FROM Sales.CustomerTotals
    ORDER BY TotalSales DESC;
END;

Applications can consume the returned rows directly.

Data Modification

Stored procedures commonly perform INSERT, UPDATE, DELETE, and MERGE operations.

Example:

CREATE PROCEDURE dbo.uspInsertCustomer
(
    @CustomerName NVARCHAR(100),
    @City NVARCHAR(50)
)
AS
BEGIN
    INSERT INTO Sales.Customers
    (
        CustomerName,
        City
    )
    VALUES
    (
        @CustomerName,
        @City
    );
END;

Using Transactions

Stored procedures frequently include explicit transactions.

Example:

CREATE PROCEDURE dbo.uspTransferFunds
AS
BEGIN
    BEGIN TRANSACTION;
    -- Debit account
    -- Credit account
    COMMIT TRANSACTION;
END;

Transactions ensure that all related operations either succeed together or fail together.

Error Handling

SQL Server supports structured error handling with TRY...CATCH.

Example:

CREATE PROCEDURE dbo.uspExample
AS
BEGIN
    BEGIN TRY
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE();
    END CATCH
END;

Functions cannot use TRY...CATCH, making this an important distinction between stored procedures and functions.

Dynamic SQL

Stored procedures can execute dynamic SQL.

Example:

CREATE PROCEDURE dbo.uspDynamicSearch
(
    @TableName SYSNAME
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL =
        N'SELECT * FROM ' + QUOTENAME(@TableName);
    EXEC sp_executesql @SQL;
END;

Using sp_executesql is generally preferred over EXEC() because it supports parameterization and helps reduce SQL injection risks.

Temporary Tables

Stored procedures often use temporary tables.

Example:

CREATE PROCEDURE dbo.uspSalesReport
AS
BEGIN
    CREATE TABLE #Sales
    (
        OrderID INT,
        Total MONEY
    );
    INSERT INTO #Sales
    SELECT
        OrderID,
        TotalAmount
    FROM Sales.Orders;
    SELECT *
    FROM #Sales;
END;

Temporary tables exist only during the session or procedure execution.

Table Variables

Stored procedures may also use table variables.

Example:

DECLARE @Orders TABLE
(
    OrderID INT,
    Total MONEY
);

Table variables are useful for storing smaller intermediate result sets.

Modifying a Stored Procedure

Use ALTER PROCEDURE.

Example:

ALTER PROCEDURE dbo.uspGetCustomers
AS
BEGIN
    SELECT
        CustomerID,
        CustomerName,
        City,
        Country
    FROM Sales.Customers;
END;

Deleting a Stored Procedure

Use:

DROP PROCEDURE dbo.uspGetCustomers;

Viewing a Stored Procedure Definition

Developers can inspect a procedure using:

sp_helptext 'dbo.uspGetCustomers';

or

SELECT OBJECT_DEFINITION
(
    OBJECT_ID('dbo.uspGetCustomers')
);

Stored Procedures vs. Functions

Stored Procedures vs. Views

Security Benefits

Stored procedures improve security by:

• Granting EXECUTE permission instead of direct table access
• Encapsulating sensitive business rules
• Reducing the application’s need for elevated privileges
• Supporting ownership chaining in many scenarios
• Helping minimize SQL injection risks through parameterized queries

Performance Considerations

Stored procedures often improve performance because:

• Execution plans can be reused.
• SQL is compiled and optimized by the query optimizer.
• Network traffic is reduced because only the procedure call is transmitted.
• Business logic executes close to the data.

However, developers should also understand parameter sniffing, where SQL Server creates an execution plan based on the parameter values supplied during compilation. In some cases, this plan may not be optimal for different parameter values. Techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, or carefully designed query patterns may help address parameter-sensitive performance issues.

AI-Enabled Database Scenarios

Stored procedures play an important role in AI-enabled database solutions.

Examples include:

• Preparing data before generating embeddings
• Coordinating vector insert and update operations
• Executing intelligent search workflows
• Managing Retrieval-Augmented Generation (RAG) data preparation
• Orchestrating multi-step AI pipelines
• Logging AI requests and responses
• Performing batch updates for AI-generated content
• Calling Azure services from application workflows that interact with the database

Stored procedures provide a reliable way to centralize business logic that supports AI applications.

Best Practices

• Use descriptive names such as uspGetCustomerOrders.
• Keep procedures focused on a single responsibility.
• Use parameterized queries whenever possible.
• Prefer sp_executesql over concatenated dynamic SQL.
• Include proper error handling with TRY...CATCH.
• Use transactions only when necessary and keep them as short as possible.
• Return only the data that callers require.
• Document business logic clearly.
• Test procedures with realistic workloads.
• Monitor execution plans and optimize when needed.

Common Exam Tips

For the DP-800 exam, remember these key points:

• Stored procedures encapsulate reusable business logic.
• Procedures can accept input parameters and output parameters.
• They can return one or more result sets.
• They support transactions, dynamic SQL, and error handling.
• CREATE PROCEDURE creates a procedure.
• ALTER PROCEDURE modifies an existing procedure.
• DROP PROCEDURE removes a procedure.
• Stored procedures can modify database data.
• Procedures cannot be referenced directly in the FROM clause like table-valued functions.
• Stored procedures are commonly used for CRUD operations, reporting, ETL, and AI workflow orchestration.

Practice Exam Questions

Question 1

A developer needs a reusable database object that can contain multiple SQL statements, modify data, and execute transactions. Which object should be created?

A. View

B. Scalar function

C. Stored procedure

D. Table-valued function

Answer: C

Explanation: Stored procedures are designed to encapsulate reusable business logic, modify data, execute transactions, and perform procedural operations.

Question 2

Which statement is used to execute an existing stored procedure?

A. EXEC

B. RUN

C. CALLPROC

D. EXECUTEQUERY

Answer: A

Explanation: SQL Server executes stored procedures using EXEC or its equivalent keyword EXECUTE.

Question 3

A stored procedure needs to return a calculated value to the calling application without including it in a result set. Which feature should be used?

A. CHECK constraint

B. OUTPUT parameter

C. DEFAULT constraint

D. Computed column

Answer: B

Explanation: OUTPUT parameters allow a stored procedure to return one or more values directly to the caller in addition to any result sets.

Question 4

Which capability is supported by stored procedures but not by user-defined functions?

A. Accepting parameters

B. Returning values

C. Executing TRY...CATCH error handling

D. Being stored in the database

Answer: C

Explanation: Stored procedures support structured error handling with TRY...CATCH, whereas user-defined functions do not.

Question 5

A developer needs to generate SQL statements dynamically while minimizing SQL injection risks. Which approach is recommended?

A. Use concatenated SQL with EXEC()

B. Store SQL in a view

C. Use a scalar function

D. Use sp_executesql with parameters

Answer: D

Explanation: sp_executesql supports parameterized dynamic SQL, improving security and enabling better plan reuse.

Question 6

Which statement modifies an existing stored procedure?

A. MODIFY PROCEDURE

B. ALTER PROCEDURE

C. UPDATE PROCEDURE

D. CHANGE PROCEDURE

Answer: B

Explanation: ALTER PROCEDURE changes the definition of an existing stored procedure without dropping and recreating it.

Question 7

Which statement about stored procedures is correct?

A. They can be referenced directly in the FROM clause of a SELECT statement.

B. They cannot accept parameters.

C. They can contain transactions and modify database data.

D. They always return exactly one scalar value.

Answer: C

Explanation: Stored procedures support transactions, data modification, and complex procedural logic. They cannot be queried directly in the FROM clause.

Question 8

What is parameter sniffing?

A. Encrypting stored procedure parameters.

B. Automatically validating parameter data types.

C. Caching parameter values for auditing.

D. Creating an execution plan based on parameter values supplied during compilation, which may not always be optimal for future executions.

Answer: D

Explanation: Parameter sniffing occurs when SQL Server optimizes a query using the initial parameter values, which can lead to less efficient plans for different parameter values.

Question 9

Which security benefit is commonly associated with stored procedures?

A. They automatically encrypt all stored data.

B. They eliminate the need for authentication.

C. They prevent all SQL injection attacks regardless of implementation.

D. They allow users to receive EXECUTE permission without requiring direct access to underlying tables.

Answer: D

Explanation: Granting EXECUTE permission on stored procedures helps restrict direct access to tables while encapsulating business logic and access patterns.

Question 10

How are stored procedures commonly used in AI-enabled database solutions?

A. They automatically generate embeddings without application logic.

B. They replace vector indexes.

C. They orchestrate reusable workflows such as preparing data, managing vector operations, logging AI activity, and supporting Retrieval-Augmented Generation (RAG) pipelines.

D. They eliminate the need for application code.

Answer: C

Explanation: Stored procedures centralize business logic and coordinate multi-step operations, making them well suited for AI-related workflows that prepare data, manage vectors, and support RAG processes.

Go to the DP-800 Exam Prep Hub main page