Lesson 13

Different Stored Procedures Conclusion

The legacy conclusion page correctly listed the core module outcomes, but it drifted into unrelated or under-explained topics (an OUTPUT clause example with inconsistent variable naming, and a glossary list with empty definitions). This rewrite tightens the module summary, aligns terminology with SQL Server 2022, and keeps the quiz link in place while improving the learning wrap-up. The OUTPUT clause section is retained as an “advanced technique” and corrected for consistency.

In this module you learned how stored procedures support enterprise business rules by centralizing data logic, improving maintainability, and enabling consistent database behavior for multiple applications. You worked through the end-to-end lifecycle of a stored procedure: definition, execution, parameterization, error handling, and testing.

Having completed this module, you should be able to:

Describe what stored procedures are and why they are used.
Explain how stored procedures can enforce business rules and data integrity.
Create, modify, and delete stored procedures using SQL Server 2022-friendly syntax.
Use input parameters and output parameters to pass values into and out of procedures.
Execute and test stored procedures using repeatable scripts in SSMS.

Module navigation

Advanced technique: capturing DML changes with the OUTPUT clause

Beyond stored procedures, Transact-SQL includes features that help you observe what a DML statement changed. The OUTPUT clause can return the affected rows from an INSERT, UPDATE, DELETE, or MERGE statement. In SQL Server 2022, this is commonly used for auditing, debugging, or returning affected keys to the caller.

In the following example, the OUTPUT clause captures values from the deleted rows into a table variable. The table variable must exist before the DML statement runs.


DECLARE @DeletedPerson TABLE
(
    BusinessEntityID int         NOT NULL PRIMARY KEY,
    LastName         varchar(50) NOT NULL,
    FirstName        varchar(50) NOT NULL
);

DELETE dbo.PersonList
OUTPUT
    deleted.BusinessEntityID,
    deleted.LastName,
    deleted.FirstName
INTO @DeletedPerson (BusinessEntityID, LastName, FirstName)
WHERE BusinessEntityID = 2;

SELECT BusinessEntityID, LastName, FirstName
FROM @DeletedPerson;


(1 row(s) affected)

BusinessEntityID  LastName        FirstName
----------------  --------------  -----------
64                Zwilling        Michael

Result for SELECT BusinessEntityID, LastName, FirstName FROM @DeletedPerson;

A more advanced form of this technique (sometimes referred to as composable DML) can feed the output into further queries (for example, inserting captured keys into another table). Use these patterns carefully and test them thoroughly, especially when triggers and transaction scopes are involved.

Glossary terms

This module introduced the following terms:

Business rules: Requirements and constraints that define how data must behave (for example, valid ranges, mandatory relationships, uniqueness, and allowed states).
Encapsulate: A design approach where database logic is wrapped in stored procedures so applications use a stable interface while internal table structures and implementation details can change over time.
Execution plan: The strategy SQL Server chooses to run a query, including operator steps, join methods, index usage, and estimated costs.
Fatal error: An error condition that prevents correct continuation of processing and requires the procedure to stop (often triggering rollback when a transaction is active).
Stored procedure: A named, compiled set of Transact-SQL statements stored in the database and executed with EXEC, optionally using parameters and structured error handling.

In the next module, you will learn about accessing remote data using modern SQL Server connectivity features.

Stored Procedure - Quiz

Before moving on, take the short multiple-choice quiz to check your understanding of the material covered in this module.

Stored Procedure - Quiz