Chapter 14: Stored Procedures and Functions

Overview

Stored procedures and functions are the most important objects of an RDBMS, outside of the engines and the database tables. The stored procedures and functions of an RDBMS, and how a product like SQL Server 2005 supports them, are what make the product. How procedures and functions are stored, compiled, optimized, and executed by a DBMS is what sets a DBMS apart from its competitors. They help us meet the rules of deployment we discussed in Chapter 13.

This chapter covers both legacy stored procedures and functions written in T-SQL as well as the procs and functions you can now install as part of the .NET Framework’s common language runtime (CLR) support (see Chapter 11). We have not cover trigger creation using the .NET Framework because the process for writing the code, compiling and installing the assembly and installing the trigger to SQL Server is identical for all “objects” CLR.

For the most part we will be discussing stored procedures because you will be creating and using them more. If you are unfamiliar with the concept of a stored procedure, you will find that the following list sheds some light on these critical SQL Server features:

• Stored procedures are collections of Transact-SQL statements or .NET Framework language assemblies that contain inline T-SQL, that can be referenced and executed by name from a client connection. They consist of functionality that executes remotely from the calling connection-the client-that is interested in exploiting the result of the remote execution.

• Stored procedures encapsulate repetitive tasks. Often in client applications a large number of SQL statements all do the same thing. One stored procedure that aecepts variables from the client can satisfy more than one query at the client, executed concurrently or at different times. More than one client can call the same stored procedure. Variable parameters that identify columns and values can replace almost all query code at the client.

• Stored procedures share application logic and code. In this respect they have a reuse benefit similar to that of classes in object-oriented software. A good example of an application that can greatly benefit from stored procedures is Report Server.

• Stored procedures hide database schema and catalog details. When you query a database using client-side SQL code, you need to know specifics of the tables and columns you are querying. This exposes the schema to the client connection and the user, especially in Internet applications. The stored procedure does not allow the client to have the proverbial foot in the door. The only information the client or connection has is the procedure name to call. In this regard, stored procedures provide a layer of security because the client also needs appropriate permissions to execute the procedure.

• Stored procedures conserve network bandwidth and allow you to concentrate processing needs at the data tier, which can be appropriately scaled up or out as needed (see Chapter 9).

• Like functions, stored procedures return values and error messages. But they can also return result sets from server-side queries that can be further processed on the server before being sent to the client. The return values can be used to indicate success or failure of stored procedure functionality, and the status can be returned to the client or used to control the flow and scope of the procedure logic.

• Stored procedures are created using the CREATE PROCEDURE statement, edited or updated using the ALTER PROCEDURE statement, and executed by the client connections; they return the result to the clients. The flow chart in Figure 14–1 illustrates the life-cycle (abridged) of the stored procedure.

  
  Figure 14–1: Requirements for stored procedures

Database developers need intimate knowledge of the workings of stored procedures. For all intents and purposes, they are to the DBMS and its databases what classes are to languages like C# and Java. Stored procedures are not inherited, derived, or cloned, nor do they sport inherited properties, methods, and the like, but they share many other valuable attributes of object-based programming such as code isolation, reuse, and sharing (by both developers and users). You cannot build any form of effective application that relies on SQL Server, nor can you be an effective DBA, without having an intimate knowledge of how to code and manage stored procedures.

Types of Stored Procedures

The several types of stored procedure supported by SQL Server are as follows:

• System   The system stored procedures are built into SQL Server and cannot be altered or tampered with short of destroying the catalog. They provide information about the database schema, object names, constraints, data types, permissions, and so on. There are several collections of system stored procedures: the catalog stored procedures, SQL Server Agent stored procedures, replication stored procedures, and so on. The system stored procedures are discussed in their respective chapters and in Appendix.

• Local   The local stored procedures, written by the DBA and SQL Server developer, are the focus of this chapter.

• Temporary   These provide the same or similar functionality as the local stored procedures discussed in this chapter, but as explained further a little later in this chapter, they only exist for the life of the connection.

• Remote   These stored procedures exist in remote servers and can be referenced by an originating server. These stored procedures are used in distributed applications.

• Extended   The extended stored procedures are similar in function to the local stored procedures but can reference functionality external to SQL Server, such as calling routines and functions in remote libraries and processes compiled, for example, in DLLs or object storehouses. For the most part extended stored procedures will be replaced by .NET Framework stored procedures.

How Stored Procedures Are Processed by SQL Server

Stored procedures are processed in two stages. In the first stage the procedure is first parsed by the SQL Server database engine (see Chapter 2) upon creation, after which two things happen. SQL Server stores the definition of the procedure, name and code, in the catalog. It also pushes the code through the Query Optimizer, as discussed in Chapter 4, and determines the best execution plan for the code.

Next the code is compiled and placed in the procedure cache. The only time the plan is flushed from the cache is when an explicit recompile is called by the client connection or the plan no longer exists in the cache, which means it had aged and had to be expelled. The cache can also be flushed via the DBCC freeproccache command discussed in Chapter 10.

In the second stage the query plan is retrieved when the stored procedure’s name is referenced in code. The procedure code is then executed in the context of each connection that called the procedure. Any result sets or return values are returned to each connection.