Stored Procedures  «Prev  Next»

Lesson 13

Different Stored Procedures Conclusion

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:
  1. Describe and define stored procedures
  2. Enforce business rules
  3. Create, modify, and delete stored procedures
  4. Use input and output parameters
  5. Test your stored procedures

Returning Data into 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
);
DELETE dbo.PersonList
OUTPUT Deleted.BusinessEntityID, Deleted.LastName,
Deleted.FirstName
INTO @DeletedPerson
WHERE BusinessEntityID = 2;

Interim result: (1 row(s) affected)
Continuing the batch 
SELECT BusinessEntityID, LastName, FirstName FROM @DeletePerson;

Result for SELECT BusinessEntityID, LastName, FirstName FROM @DeletePerson
Result for 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

Glossary terms

This module introduced you to the following terms:
  1. 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.
  2. 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.
  3. Execution plan:
  4. Fatal error:
  5. Stored procedure:
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