This module discussed how you can use the different types of stored procedures to enforce business rules and make your life easier. Having completed this module, you should be able to:
Describe and define stored procedures
Enforce business rules
Create, modify, and delete stored procedures
Use input and output parameters
Test your stored procedures
Returning Data into a Table
For T-SQL developers, the OUTPUT clause can return the data for use within a batch or stored procedure. The data is received into a user table, temp table, or table variable, which
must already have been created. Although the syntax may seem similar to the INSERT...INTO syntax, it actually functions differently.
In the following example, the OUTPUT clause passes the results to a @DeletedPerson table variable
DECLARE @DeletedPerson TABLE (
BusinessEntityID INT NOT NULL PRIMARY KEY,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50) NOT NULL
OUTPUT Deleted.BusinessEntityID, Deleted.LastName,
WHERE BusinessEntityID = 2;
Interim result: (1 row(s) affected)
Continuing the batch
SELECT BusinessEntityID, LastName, FirstName FROM @DeletePerson;
An advance use of the OUTPUT clause, called composable DML, passes the output data to an outer
query, which can then be used in an INSERT command
This module introduced you to the following terms:
Business rules:These rules are intended to prevent disruption in a company or business. Business Rules are used every day to define entities, attributes, relationships and constraints.
Encapsulate:A database encapsulation layer hides the implementation details of your database, including their physical schemas, from your business code.
This layer provides your business objects with persistence services. Ideally your business objects should not know anything about how they are persisted, it just happens.
Database encapsulation layers are commonly used practice by both large and small applications as well as in both simple and complex applications.
In the next module, learn about accessing remote data with SQL Server 2000.
Stored Procedure - Quiz
Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz. Stored Procedure - Quiz