Programming Stored Procedures

Data Driven

LANGUAGES: VB | TSQL

TECHNOLOGIES: ADO.NET | SQL Server 2000 | Stored Procedures

Programming Stored Procedures

You Are Using Stored Procedures, Aren t You?

By Wayne S. Freeze

If you re not using stored procedures in your database application, you re probably not exploiting the full power of your database. The decision to use stored procedures should be simple, but many people don t really understand what stored procedures are or why they re helpful and therefore are reluctant to use them. In this article, I ll provide some practical examples of how stored procedures can make your life easier.

What Is a Stored Procedure?

A stored procedure is a precompiled collection of SQL statements stored on the database server. Stored procedures typically are written in a combination of SQL statements and control language that varies from database vendor to database vendor. Not all database systems support stored procedures. For instance, the Jet database engine in Microsoft Access doesn t support stored procedures. Nor does MySQL. However, most server-based database systems, such as SQL Server, Oracle, Informix, and DB2, do support stored procedures.

To understand why you should use a stored procedure, look at what happens when you submit a SQL statement to the database server for processing (see FIGURE 1). When the database receives the SQL statement, the database must compile it before it can be executed. If the statement is a Select statement, the database server will run the query through the query optimizer to choose the most efficient way to get the data. Once the query is compiled, the execution engine runs the query, and the results return to the application.

FIGURE 1: Running SQL statements vs. stored procedures.

Although it doesn t take many resources to compile the query, the query optimizer is another story. The query optimizer takes many resources to determine the optimal way to retrieve the data. The optimizer will look at various statistics captured by the database server, as well as information about the distribution of data for the various indexes associated with each table.

When you create a stored procedure, the database server compiles and optimizes the query and stores the result in the database. This means that when you run the query, all the database server has to do is retrieve the compiled and optimized code from the database and run it. In most situations, this represents a significant savings of resources.

A stored procedure is more efficient than executing individual SQL statements directly in your program, but that s not the only reason you should use one. Because stored procedures are written in a mixture of SQL statements and control statements, you can create complex stored procedures that function like subroutines. Stored procedures can contain parameters, just like subroutines, which makes them much more flexible than ad hoc SQL statements.

Each call your application makes to the database server comes with a lot of overhead, and the ability to combine several tasks into a single database call will improve your application s performance. At the same time, the ability to combine tasks into a single call reduces the amount of work the database server has to perform. This obviously applies when you have a complex update process, but it can even save resources when retrieving data. This is true because most database servers allow the stored procedure to return multiple sets of data from multiple Select statements. Thus, with one stored procedure call, you can gather enough information to create a complex Web page.

Stored procedures can create a level of independence between your application and the database server. As long as the stored procedure accepts the parameters in the same order, you can rewrite the stored procedure to suit the specific database server you re using. This also has the side effect of creating standardized ways to access your data, which will simplify database access from your application.

Another advantage of stored procedures is that they can be secured independently of other objects in your database. Thus, you could prevent someone from using the Delete statement from SQL, but you could grant him or her access to a stored procedure that uses the Delete statement. This enforces database integrity because the only way you will permit someone to make a change to the database is through a set of stored procedures that will perform the update properly.

Creating Stored Procedures

Before you can call a stored procedure, you need to create it. Because stored procedures reside in the database, you ll typically use the database s utilities to create and test it. Although it is possible to use Visual Studio, typically you ll find that the tools specific to a particular database work better than a general-purpose tool such as Visual Studio. The examples in this article are based on SQL Server 2000, but the basic concepts apply to all of the databases that support stored procedures.

Most database systems use the SQL statement Create Procedure as one way to create stored procedures. The syntax for this statement varies slightly from database system to database system, but, for the most part, it will look like this:

Create Procedure []

As

The Create Procedure keywords must begin the statement, followed by the name of the procedure. The parameter list is optional and, if present, must precede the As keyword, after which is the list of SQL statements that will be executed each time you call the stored procedure.

This is an example of a simple stored procedure that uses the Select statement to retrieve a set of records from the database. Note that the list of parameters is omitted because this procedure was designed to retrieve all of the rows from the CDs table:

Create Procedure GetCDList As

   Select CDId, CDTitle, ArtistName

   From CDs c, Artists a

   Where c.ArtistId = a.ArtistId

   Order By  ArtistName, CDTitle

However, this stored procedure is somewhat unrealistic because of the number of rows it could retrieve. Typically, you will create stored procedures that retrieve only a small number of related rows, like this stored procedure:

Create Procedure GetTracks

   @CDId Int

As

   Select TrackId, TrackTitle

   From Tracks

   Where CDId = @CDId

   Order By TrackId

This stored procedure takes a single parameter @CDId, which has a type of Int. SQL Server requires parameters to begin with @. This parameter is used as a value in the Where clause to select only those tracks that have a CDId which matches the value in @CDId. In general, input parameters may be used any place a constant value may be used.

Like subroutines in Visual Basic, the parameters to a stored procedure can be used to return a value from it as well as to pass values to it. Also like VB .NET, parameters are assumed to be input-only (ByVal) unless you explicitly override them. In SQL Server, you indicate that a parameter can return a value by specifying the Output keyword after the parameter s data type.

Because it takes more resources to return a set of records than it does to return a set of values using the parameters, you may want to create stored procedures that return a single row of information through its parameters. Also, if you need to return information about a single object in your database, as well as the set of rows related to it, you might use a stored procedure like that shown in FIGURE 2.

Create Procedure GetCDDetails

   @CDId Int,

   @CDTitle Varchar(128) Output,

   @CDArtist Varchar(128) Output,

   @CDArtistId Int Output

As

   Select t.TrackId, t.TrackTitle,

      Case When c.ArtistId = t.ArtistId Then ''

      Else '(' + a.ArtistName + ')'

      End TrackArtist

   From Tracks t, Artists a, CDs c

   Where c.CDId = @CDId And t.CDId = @CDId

   And t.ArtistId = a.ArtistId

   Order By t.TrackId

   Select  @CDTitle = c.CDTitle, @CDArtist = a.ArtistName,

   @CDArtistId = c.ArtistId

   From CDs c, Artists a

   Where c.CDId = @CDId  And c.ArtistId = a.ArtistId

FIGURE 2: A stored procedure that returns information about a single object.

This stored procedure accepts a single input parameter, @CDId, and returns a set of records associated with that particular value by the first Select statement. This statement creates a calculated column called TrackArtist. It will only contain a value when the ArtistId value in the CDs table differs from the ArtistId value associated with the specific track on the CD.

The three other parameters, @CDTitle, @CDArtist, and @CDArtistId, are output-only and get their values from the second Select statement. A value is assigned to each parameter by inserting the name of the parameter and an equals sign (=) in front of each column following the Select keyword. It is important that the Select statement return a single row. If multiple rows are returned, the last value retrieved will be the value assigned to the parameter.

Calling a Stored Procedure

To get information from the database using a stored procedure, you ll use the same basic techniques you use when you execute an ad hoc SQL statement. Specifically, create a SqlCommand object (or OleDbCommand object if you are not using SQL Server) with the appropriate information, like this:

Dim ds As New DataSet

Dim conn As New SqlConnection(ConnectionStr)

Dim adpt As New SqlDataAdapter

adpt.SelectCommand = New SqlCommand("GetCDList", conn)

adpt.Fill(ds, "CDList")

This code fragment begins by creating a new DataSet object, which will hold the data retrieved from the stored procedure. Then, the code creates a new SqlConnection object, which uses the information stored in the ConnectionStr constant to open a path to the database. Finally, a new SqlDataAdapter is created to provide the necessary interface between the DataSet object and the database server.

Next, a new SqlCommand object is created (using the name of the stored procedure where you normally would specify a SQL statement) in addition to the SqlConnection object created earlier. Finally, the SqlDataAdapter s Fill method is used to load the data the stored procedure has generated into the DataSet object.

The information from the DataSet is bound to a DataList control using this code:

DataList1.DataSource = ds.Tables("CDList").DefaultView

DataList1.DataBind()

The DataList control is shown in FIGURE 3; the result of running the code is shown in FIGURE 4.

    cellspacing="0" cellpadding="0"

    style='bordercolor="#C0C0C0" bgcolor="#FFFFC9"

    valign="top"' width="100%"

    runat="server">

      <%# Container.DataItem("ArtistName") %>,

       <%# Container.DataItem("CDTitle") %>">

FIGURE 3: Adding a DataList control to display the list of CD titles in the database.

FIGURE 4: Displaying information extracted from the database with a stored procedure.

Calling a Stored Procedure with Parameters

To call a stored procedure with parameters, you need to define each of the parameters and assign them values before you call the stored procedure. In this code, you create a separate SqlCommand object and specify the name of the stored procedure and the SqlConnection object. Once the command object has been created, its CommandType property is set to CommandType.StoredProcedure. This is optional for stored procedures without parameters, but you must set this property if your stored procedure contains parameters. An example of calling a stored procedure from ADO.NET is shown in FIGURE 5.

Dim ds As New DataSet

Dim conn As New SqlConnection(ConnectionStr)

Dim adpt As New SqlDataAdapter

Dim cmd As New SqlCommand("GetCDDetails", conn)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@CDId", SqlDbType.Int, _

 4).Value = CInt(Request.QueryString("CDId"))

cmd.Parameters.Add("@CDTitle", SqlDbType.Varchar, _

 128).Value = ""

cmd.Parameters("@CDTitle").Direction = _

 ParameterDirection.Output

cmd.Parameters.Add("@CDArtist", SqlDbType.Varchar, _

 128).Value = ""

cmd.Parameters("@CDArtist").Direction = _

 ParameterDirection.Output

cmd.Parameters.Add("@CDArtistId", SqlDbType.Int).Value = 0

cmd.Parameters("@CDArtistId").Direction = _

 ParameterDirection.Output

FIGURE 5: Initializing the objects needed to call the stored procedure.

After CommandType is set, you must explicitly add each parameter to the Parameters collection associated with the SqlCommand object. The easiest way to do this is to use the Add method and specify the name of the parameter and its type. If you specify a type that has a length attribute like Varchar, you must specify its length also. One nice thing about the Add method is that it returns a SqlParameter object, which means that you can reference the Value property and assign the parameter a value in a single line of code. For Output parameters, you must explicitly specify ParameterDirection.Output for the Direction property. Otherwise, the parameter is treated as an input parameter.

Once the definition of the command is complete, you can use code similar to the following to execute the command object and call the stored procedure:

adpt.SelectCommand = cmd

adpt.Fill(ds, "CDDetails")

DataList1.DataSource = ds.Tables("CDDetails").DefaultView

DataList1.DataBind()

Label1.Text = cmd.Parameters("@CDTitle").Value

If cmd.Parameters("@CDArtistId").Value > 0 Then

   Label1.Text &= ": " & cmd.Parameters("@CDArtist").Value

End If

The command object is assigned directly to the SelectCommand property of the SqlDataAdapter object, and the Fill method will run the command to populate the DataSet. The steps to bind the DataSet to the DataList control are the same as before. A Label control is used to display the values of the output parameters.

The code associated with the Label and DataList controls is shown in FIGURE 6. Although the Label control precedes the DataList control in the HTML listing, the code to populate the Label control follows the code for the DataList. The order doesn t matter because the Web page isn t fully generated until all of the code has completed. The output generated by this code is shown in FIGURE 7.

    cellspacing="0" cellpadding="0"

    style='bordercolor="#C0C0C0" bgcolor="#FFFFC9"

    valign="top"' width="100%"

    runat="server">

   <%# Container.DataItem("TrackTitle") %>

   <%# Container.DataItem("TrackArtist") %>

FIGURE 6: Using a second DataList control to hold the details about a particular CD.

FIGURE 7: Displaying the results of a complex, stored procedure.

Conclusion

Over the years, I ve run into many sites that refuse to use stored procedures for one reason or another. Depending on the volume of traffic to the Web site, that may be acceptable. However, stored procedures should be a key part of any database-oriented Web site because they are more efficient than issuing SQL statements directly from your program. Also, they allow you to execute multiple SQL statements in a single database request. The ability to pass and return parameters from a stored procedure is very important because it allows you to create more flexible code.

Stored procedures can be used anywhere a SqlCommand or OleDbCommand can be used in your application. In fact, once the stored procedure has been defined, it can be called multiple times in your program, even if it uses parameters. Just assign new values to the parameters before each call. Of course, stored procedures are tightly coupled to the brand of database server you use. This makes it somewhat harder to move your application from one database server to another. Although you may have to rewrite the actual stored procedures, your ASP.NET application likely will continue to run unchanged.

You can download all the code, along with the sample database (see details at the end of the article). The database is packaged as a Jet (Access) database. Just use DTS to copy the tables from the Jet database to your SQL Server database. Then, use Query Analyzer to add the stored procedures. Finally, copy the aspx and jpg files to your Web server and modify the information in the ConnectionStr constant to access your database. Now, you should be ready to run. In next month s column, I ll deal with an issue that affects many database developers: how to break a large block of data into a series of smaller pages and allow the user to navigate through the data one page at a time. In the meantime, if you have questions about this article or subjects you would like to see me address in future articles, please send me an e-mail I d love to hear from you.

The files referenced in this article are available for download.

Wayne S. Freeze is a full-time computer-book author with more than a dozen titles to his credit, including ASP.NET Database Programming Bible (Hungry Minds 2001), Windows Game Programming with Visual Basic and DirectX (QUE, 2001), and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds, 2000). He has nearly 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master s degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail at mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.

Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.