Stored Procedures Exercise

  • Stored procedures are "pre-compiled" Transact-SQL statements

  • Stored in the database in various systems tables

  • There are both system and user stored procedures

  • Some of the functionality of Enterprise Manager is delivered through stored procedures

Purpose of this module:

At the completion of this module, you should be able to

  1. Explain why and when stored procedures are useful

  2. Create stored procedures

  3. Execute stored procedures

  4. Pass parameters to stored procedures

 Why Stored Procedures are Useful

They perform better than plain queries because

  • When a stored procedure is created, it is “pre-compiled” (not really pre-compiled, but that's what many call the following):

    • References to other database objects (tables, etc.) are resolved (checked to make sure they exist)

    • The name of the stored procedure is stored in a system table, the code in another

    • A plan for how best to execute the query is developed and stored in yet another system table

    • When the procedure is run for the first time, all the information about it is stored in the system cache, which makes it much faster to run subsequently

  • Compared to a series of queries passed from a client application, they require much less network traffic and therefore may run much more quickly

  • They make maintenance of code much simpler because it is all managed in one place. Stored procedures can be re-used by multiple applications.

  • Stored procedures are an excellent way of encapsulating business rules and making these rules available to many applications

 

There are various opinions on the value of stored procedures. See the following for more details:

Stored procedures are bad, m'kay?

Don't use stored procedures yet? Must be suffering from NIHS.

 

Stored Procedure Exercise

Part One of this exercise will show you how to:

  • Create a simple stored procedure

  • Execute the stored procedure

Directions

Stored procedures use Transact-SQL to “create” themselves. To create a stored procedure called prGetCustomers that simply returns all rows in the Customers table:

  1. Select the Northwind database assigned to you (NW followed by your userid)

  2. Open the Query Analyzer

  3. Type the following

Create procedure prGetCustomers

As

            Select * from customers

  1. Now, execute this stored procedure:

  2. Type

Execute prGetCustomers

 

The results will be displayed in the lower part of the query analyzer window.

 

 Part Two of this exercise requires you to determine how to pass parameters to a stored procedure.

 

Hints:

declare parameters by placing the "at" sign (@)

Now, figure out how to create a stored procedure that accepts parameter values.

 

Use the following for help: