What Are Stored Procedures?

for RuBoard

In the Microsoft SQL Server documentation, a stored procedure is defined as "a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit." Put more simply, a stored procedure is like a method or functionit performs a set of actions on the data in your database. They normally encapsulate an area of functionality, such as adding a new record to a table, or returning a specific set of information.

Stored procedures offer a number of benefits. First, because they are precompiled, they generally perform better than a straight SQL call directly from your application. Additionally, by channeling all data access in your application through a set of stored procedures, you can more easily debug and manage your code. Also, keep in mind that changes made in a stored procedure do not directly require you to recompile your application.

To add a new stored procedure to the Northwind Microsoft SQL database, perform these steps:

  1. Load SQL Server Enterprise Manager, found in the Microsoft SQL Server program group .

  2. Expand the tree "Microsoft SQL Servers," "SQL Server Group," and locate your database server. With SQL installed locally, the default server will be (LOCAL) as seen in Figure 15.1.

    Figure 15.1. The Northwind database seen in the Microsoft SQL Enterprise Manager.

    graphics/15fig01.jpg

  3. Locate and expand the Northwind database. Select Stored Procedures. On the right side of the screen, you'll see any default stored procedures included with the Northwind database.

  4. Right-click anywhere on the right pane of the Enterprise Manager and select New Stored Procedure. As shown in Figure 15.2, this brings up a very basic text editor that will enable you to input your stored procedure.

    Figure 15.2. Creating a new stored procedure using Microsoft SQL Enterprise Manager.

    graphics/15fig02.jpg

  5. Erase the default text in the stored procedure window and input the code from Listing 15.1. After you select OK, a very simple stored procedure is created that will perform a query and return the resultset.

Listing 15.1 A Simple Stored Procedure
 CREATE PROCEDURE [Customers_Get] AS SELECT     CustomerID,     CompanyName,     ContactName,     Address,     City,     Phone FROM     Customers 

There are several ways to test the stored procedure. Probably the simplest way to test is to load up another of SQL Server's management tools called the Query Analyzer. It can be found in the same program group as the Enterprise Manager. After loading the Query Analyzer, enter (local) as your servername if SQL Server is running locally. Otherwise, enter the name of your server. After the Query Analyzer is loaded, your screen will look like Figure 15.3.

Figure 15.3. The SQL Query Analyzer also enables you to create stored procedures.

graphics/15fig03.jpg

Before you can test the stored procedure, you first must select the Northwind database from the database drop-down list. Now, the Query Analyzer is ready to process SQL statements against Northwind. You can run any type of SQL statement against the database from this window. To run a stored procedure, you can simply type the following:

 exec stored_procedure_name 

If your stored procedure accepts any arguments, you can add them after the stored procedure's name, separated by commas. If the stored procedure returns results, they will be displayed in the bottom half of the screen of the Query Analyzer, as seen in Figure 15.4.

Figure 15.4. The SQL Query Analyzer displays the results of a query.

graphics/15fig04.jpg

graphics/pencil.gif

This section only scratches the surface of what the Query Analyzer is capable of. You can actually use the Query Analyzer to optimize table indexes and to display SQL Server's execution plan for your query, along with the time it takes to perform each step. This is an invaluable tool in optimizing your queries. For more information, consult Microsoft SQL Server Books Online, found in the Microsoft SQL Server program group.


In addition to returning a resultset, it's possible to pass specific values into and out of the stored procedure, just like method arguments. Stored procedure arguments, called parameters, are defined just after the stored procedure name, as you can see in Listing 15.2. Stored procedure variables always have the "@" symbol prepended to their name. Following the parameter declaration, the data type and length of the parameter is declared, as well. The stored procedure in Listing 15.2 takes a CustomerID as a parameter. The parameter is used in the WHERE portion of the query. Only customers with the CustomerID you pass in will be returned by the query.

Listing 15.2 A Stored Procedure with an Input Parameter
 CREATE PROCEDURE Customer_GetByID (     @CustomerID nchar(5) ) AS SELECT     ContactTitle,     ContactName,     CompanyName,     Address,     City,     Region,     PostalCode,     Phone FROM     Customers WHERE     CustomerID = @CustomerID 

It's also possible to create a parameter used to return extra information. These are called output parameters. By simply placing the OUTPUT keyword after the parameters' type declaration, you can use the parameter to store specific information to return.

Now that you've seen how to create and test stored procedures, it's time to see how to work with stored procedures using ADO.NET.

for RuBoard


Sams Teach Yourself ADO. NET in 24 Hours
Sams Teach Yourself ADO.NET in 24 Hours
ISBN: 0672323834
EAN: 2147483647
Year: 2002
Pages: 237

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net