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:
Load SQL Server Enterprise Manager, found in the Microsoft SQL Server program group .
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.
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.
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.
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.
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.
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:
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.
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.
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.