Chapter 9. Stored Procedures and User-Defined Functions

Chapter 9. Stored Procedures and User -Defined Functions

O BJECTIVES

This chapter is split into two major topics. First, it covers stored procedures and then user-defined functions. This means the five objectives are split up into ten smaller pieces, half for stored procedures and half for user-defined functions. It's a good logical break, but it doesn't necessarily get along with how the objectives are laid out. This chapter isn't ordered the way the objectives are written, but by how you need to learn the material.

Enforce procedural business logic using stored procedures and user-defined functions.

• Playing the role of the middle tiers in n- tier architecture, stored procedures and user-defined functions provide facilities for validating and modifying data to conform to business rules.

Pass and return parameters to and from stored procedures and user-defined functions.

• To control what your stored procedures and functions do, you can pass them different values that control how they work, called parameters. In addition, parameters provide a way to retrieve the data found by your stored procedures and user-defined functions.

Manage data manipulation by using stored procedures and user-defined functions

• Stored procedures and user-defined functions can be used to manipulate and store data by encapsulating SELECT , INSERT , UPDATE , and DELETE functionality.

Filter data by using stored procedures and user-defined functions.

• Web-based applications commonly use stored procedures and user-defined functions to determine which data will be displayed, taking advantage of the cached plans and easier management found in stored procedures.

Implement error handling in stored procedures and user-defined functions.

• Error handling is very important if you want to achieve true data integrity and provide feedback to the user. Stored procedures and user-defined functions provide capabilities to handle many errors and provide this feedback.

O UTLINE

Managing Data Manipulation Using Stored Procedures

What Is a Stored Procedure?

Creating and Managing Stored Procedures

Stored Procedure Groups

Calling Stored Procedures; Passing and Returning Parameters To and From Stored Procedures

Stored Procedure Scoping

Stored Procedures and Temporary Tables

Dropping Stored Procedures

Altering a Stored Procedure

Stored Procedure Text

Nested Stored Procedures

Enforcing Procedural Business Logic with Stored Procedures

Filtering Data Using Stored Procedures

Implementing Error Handling in Stored Procedures

Managing Data Manipulation with User-Defined Functions

Types of User-Defined Functions

Restrictions on User-Defined Functions

Creating User-Defined Functions

Dropping and Altering User-Defined Functions

Determinism and Functions

When to Use Functions or Stored Procedures

Passing and Returning Parameters To and From User-Defined Functions

Enforcing Procedural Business Logic Using User-Defined Functions

Filtering Data with User-Defined Functions

Implementing Error Handling in User-Defined Functions

Apply Your Knowledge

Exercises

Review Questions

Exam Questions

Answers to Review Questions

Answers to Exam Questions

S TUDY S TRATEGIES

• As in Chapter 6, "Programming SQL Server 2000," practicing the techniques shown in this chapter will help you understand the material.

• If available, find stored procedures from peers and coworkers that operate on a database you have some familiarity with and review those stored procedures and functions.

• Spend some time looking at the different ways to pass parameters into stored procedures and how optional parameters work. You're almost guaranteed to have one exam question that revolves around positional and named parameters and default values.