Creating Stored Procedures | Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)

Chapter 8 - Using Stored Procedures

byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

Stored procedures are not difficult to create - at least in theory - but they do require some knowledge of the basic rules and syntax. In this section, we'll create a stored procedure and test it with MSDE. Later on, we'll put it through its paces using ASP.NET and ADO.NET.

At this point, our decision to use MSDE as our RDBMS makes things a little trickier than they could otherwise be. However, creating stored procedures still isn't very difficult, and there are no self-imposed restrictions on the range of stored procedures it's possible to create. If you have access to the Professional Edition of the Visual Basic .NET software, or the full version of SQL Server, there are a couple of visual tools that you might like to know about.

If you have the full version of SQL Server installed, the Query Analyzer is an extremely useful tool for SQL development. Among other things, it allows you to create and enter procedures directly into the database, and will check your SQL syntax too. It enables you to view database objects (such as tables, views, and stored procedures), to run SQL queries, and to test other objects.

If you have the Professional Edition of Visual Studio installed, the Server Explorer gains some features over and above those present in the Standard Edition. As we'll see shortly, the latter allows you to discover the names and execute stored procedures, but the Professional version permits viewing and editing of the procedures already present in the database, and enables you to create your own with just a few clicks of the mouse.

In this book, however, we're using MSDE, which means that we have to take a slightly different approach. Creating a stored procedure requires the execution of an SQL command, but while the tools we just discussed allow you to do that visually, with MSDE we have to write the procedures by hand, and execute them using a simple VB.NET application. Thankfully, that's not as difficult as it sounds, as you'll begin to see in the next section.

Creating a Stored Procedure in MSDE

In SQL code, a stored procedure is created in a similar fashion to a table - where a table uses CREATE TABLE, a stored procedure uses CREATE PROCEDURE. The most basic syntax for creating a stored procedure is therefore as follows:

    CREATE PROCEDURE proc_name AS statement

All we need to provide is a procedure name (proc_name), and any kind of SQL statement or statements that we want the procedure to execute. As we discussed above, it is not mandatory that we have to return a result set from a stored procedure, but doing so is common, and makes for an example that's easier to grasp! The following line could be used to create a stored procedure called sp_GetCustomers that returns a result set containing all of the records in the Customers table of the Northwind database:

    CREATE PROCEDURE sp_GetCustomers AS SELECT * FROM Customers

In accordance with naming conventions for stored procedures, we prefix the name of the procedure with sp_. After execution of this command, the stored procedure will have been created in the database. We could then execute the stored procedure itself by using the following SQL EXECUTE command:

    EXECUTE sp_GetCustomers

Of course, this isn't the limit of what's possible with stored procedures - if it was, there would be little point in creating and using them. A significant advance from this stage is the fact that, as we've mentioned, we can provide stored procedures with parameters, just as we can do with a VB.NET function. For our first example, though, we'll keep things straightforward.

Creating Stored Procedures with MSDE

In all of the examples we've looked at so far, we've been happy to use a text editor as our development tool, as this has enabled us to see most clearly exactly what's going on. From this point, however, we're going to need some of the more advanced tools that Visual Studio brings to the party. In Visual Studio, open up the Server Explorer window, and find your way to the Northwind database. As you can see, the database comes complete with seven stored procedures, including the Ten Most Expensive Products procedure we just looked at:

click to expand

The Server Explorer window provides us with the useful ability to execute a significant proportion of stored procedures in place, without the need to construct a web page or Visual Basic application around them. To see this in action, right click on Ten Most Expensive Products, choose Run Stored Procedure from the context menu, and keep an eye on the Output window. Here's what you'll see:

    Running dbo."Ten Most Expensive Products".    TenMostExpensiveProducts                 UnitPrice    ---------------------------------------- ---------    Côte de Blaye                            263.5    Thüringer Rostbratwurst                  123.79    Mishi Kobe Niku                          97    Sir Rodney's Marmalade                   81    Carnarvon Tigers                         62.5    Raclette Courdavault                     55    Manjimup Dried Apples                    53    Tarte au sucre                           49.3    Ipoh Coffee                              46    Rössle Sauerkraut                        45.6    No more results.    (10 row(s) returned)    RETURN_VALUE = 0    Finished running dbo."Ten Most Expensive Products".

As we begin to create our own stored procedures, this facility will save time by letting us test them quickly. It's about time, then, that we gave it a try. In the following examples, we'll create and test a brand new stored procedure in the Northwind database.

Try It Out - Creating a Basic Stored Procedure

Our brief here is to write a stored procedure that selects the IDs and names of all the Northwind customers with mailing addresses in Washington State. From our knowledge of the Customers table, we know that we'll need to filter records on the basis of values in the Region field. This example will demonstrate how to proceed from that point.

To begin with, we need to write an SQL query that will return the CustomerID and CompanyName fields of the records in question:
```
 SELECT CustomerID, CompanyName FROM Customers WHERE Region = 'WA' 
```
Next, we need to insert this query into the database as a stored procedure - and to do that, we need to write some Visual Basic code. In VB.NET, create a new console application called CreateSP.

To the skeleton application generated by the Wizard, you need to add the following code. As you can see (and as we'll discuss later), it shares a number of features with the ASP.NET code we've been writing.

 Imports System Imports System.Data Imports System.Data.SqlClient Module Modulel   Sub Main()     Dim cn As SqlConnection     Dim sql As String     Dim cmd As SqlCommand     cn = New SqlConnection("Data Source=(local)\NetSDK;" & _                            "Initial Catalog=Northwind;Integrated Security=SSPI")     cn.Open()     sql = "CREATE PROCEDURE sp_WaCustomers AS " & _           "SELECT CustomerID, CompanyName FROM Customers WHERE Region = 'WA'"     cmd = New SqlCommand(sql, cn)     cmd.ExecuteNonQuery()     Console.WriteLine("Procedure created!")   End Sub End Module

Obviously, the string that you provide as the data source can vary depending on your particular setup. Here, it's the database on the local machine that we've been using throughout our examples so far.

With that done, compile and run this code, and then refresh the view in the Server Explorer. You should see the new stored procedure appear in the list:
Finally, to prove that our stored procedure works, we can again use Visual Studio .NET to run it. When you choose Run Stored Procedure from the context menu, you'll be presented with the following:

How It Works

We've already looked at most of the processes taking place here, but one aspect that merits a little further explanation is the VB.NET code that inserts the stored procedure in the database. We'll be reusing this code for the other examples in this chapter:

         Dim cn As SqlConnection         Dim sql As String         Dim cmd As SqlCommand         cn = New SqlConnection("Data Source=(local)\NetSDK;" & _                                "Initial Catalog=Northwind;Integrated Security=SSPI")         cn.Open()         sql = "CREATE PROCEDURE sp_WaCustomers AS " & _               "SELECT CustomerID, CompanyName FROM Customers WHERE Region = 'WA'"         cmd = New SqlCommand(sql, cn)         cmd.ExecuteNonQuery()         Console.WriteLine("Procedure created!")

This familiar-looking sequence of instructions contains one significant change from our usual ASP.NET SQL-executing code: we use the command object's ExecuteNonQuery() method, rather than ExecuteReader(). As you know, the latter returns a data reader object that can be used elsewhere in your code; but when you're just performing an operation on the database, ExecuteNonQuery() is the method to use.

Amending Stored Procedures with MSDE

Fresh from your early success, you might think about trying to modify the statement so that (say) the results are ordered by CompanyName, in descending alphabetical order. A reasonable first attempt at this would surely be to make the following change to the VB.NET application:

         sql = "CREATE PROCEDURE sp_WaCustomers AS " & _               "SELECT CustomerID, CompanyName FROM Customers " & _               "WHERE Region = 'WA' ORDER BY CompanyName DESC"

If you were to try to run this new code, however, you'd get the following error:

 An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error.

Here, we're being told that we're trying to add a procedure that already exists; this is an illegal operation. If we want to create a new stored procedure with the same name as an old one, there are two ways of dealing with it: we can either DROP the old procedure and create a new one, or ALTER an existing procedure. Let's look at these in turn.

First, we could call DROP PROCEDURE, and then use a CREATE PROCEDURE statement to create a new stored procedure. This would delete the existing procedure and create the procedure as if it had never existed.

         cn = New SqlConnection("Data Source=(local)\NetSDK;" & _                                "Initial Catalog=Northwind;Integrated Security=SSPI")         cn.Open()         sql = "DROP PROCEDURE sp_WaCustomers"         cmd = New SqlCommand(sql, cn)         cmd.ExecuteNonQuery()         Console.WriteLine("Procedure deleted!")         sql = "CREATE PROCEDURE sp_WaCustomers AS " & _               "SELECT CustomerID, CompanyName FROM Customers " & _               "WHERE Region = 'WA' ORDER BY CompanyName DESC"         cmd = New SqlCommand(sql, cn)         cmd.ExecuteNonQuery()         Console.WriteLine("Procedure created!")

This method does have a side effect: if we've assigned permissions to the procedure, we'll lose them as a result of the DROP PROCEDURE statement. If we really want to start from scratch, DROP is the way to go, but if we want to retain permissions on the procedure, it's better to alter it using an ALTER PROCEDURE statement, like this:

         cn = New SqlConnection("Data Source=(local)\NetSDK;" & _                                "Initial Catalog=Northwind;Integrated Security=SSPI")         cn.Open()         sql = "ALTER PROCEDURE sp_WaCustomers AS " & _               "SELECT CustomerID, CompanyName FROM Customers " & _               "WHERE Region = 'WA' ORDER BY CompanyName ASC"         cmd = New SqlCommand(sql, cn)         cmd.ExecuteNonQuery()         Console.WriteLine("Procedure created!")

This should reverse the order of the data rows back again - test it yourself, if you like. Finally, you need to be aware that you'll get an error if you try to use either of these techniques when sp_WaCustomers does not exist. You've got to create the stored procedure in the first place.

Versions of Visual Studio .NET above the Standard Edition have a Delete option in the Server Explorer's context menu, but here we've looked at SQL solutions that are applicable regardless of the development environment you're using.