The Stored Procedure Plan

Your stored procedure plan is almost identical in objective and scope to the trigger plan discussed in chapter 13. It should play as big a part, if not bigger, in the overall database architecture and modeling. If you have not gone over the trigger plan suggestions made earlier, or if you do not use a formal plan f or the production of stored procedures, now is the time to adopt this practice. You can use the flow diagram in Figure 14–2 as a starting point to build your stored procedure plan. The steps in the plan are explained in the next sections.

Figure 14–2: Modeling the stored procedure

Document and Determine Need for Stored Procedures

We have looked at some of the reasons to use stored procedures earlier and at length in Chapter 10. This is the section of your plan (especially if you are motivated to move away from a desktop solution like Microsoft Access) to list and discuss the needs and reasons.

Identify Key Stored Procedure Issues to Cover

The issues to be catered to by stored procedures, or the solutions to be obtained using stored procedures, must be referenced or become part of the database architecture. (In the trigger plan, I also listed a number of issues you should cover here, so go back and apply the points there to the stored procedure plan.)

One important area to address is the development tools you use to create and debug stored procedures. You can use query windows in Management Studio, which you probably will do to write, edit, debug, and performance-test your code as described in this chapter. A few third-party SQL Integrated Development Environments (IDEs) on the market are specifically suited to SQL Server. They are worthy tools for the DBA or SQL Server developer who writes a lot of stored procedures, and perhaps some very complex ones as well. The serious developer who needs to fully step through code will use Visual Studio.

The following list provides an example of some stored procedure issues to consider in the stored procedure plan:

• Who will develop and maintain the code?

• Will we use encryption?

• What factors determine recompilation?

• How do we handle error messages and track bugs raised by the code?

• How will we monitor and assess performance and service level?

• When would we need to use an extended stored procedure, and who will create it?

• What development tools and debugging environment should we use?

List the Stored Procedures and Their Objectives

In this section, list the precise objective of each procedure you need to create. If you are converting legacy in-line SQL statements still buried in your client applications, a good way to start is to go through all the client procedures and copy the SQL statements from them. Paste each statement into a document, noting exactly where in the client code the statement is buried, and mark the statement for replacement by the stored procedure. Doing it this way can help you identify SQL statements in your code that are the same or similar and could thus make use of one stored procedure, or a variation of one or more.

In many cases, you will find that one stored procedure just needs new parameters and can be used to satisfy a number of areas in the client code. This exercise is rewarding because the code in the client application can drop dramatically For example, a few dozen one-line EXEC PROC statements can reduce the number of code lines by several thousand.

Fully Document the Solution for Each Stored Procedure

This section is as important for stored procedures as triggers. If you have not read the trigger plan earlier and you intend to prepare a stored procedure plan, you should read the trigger plan first.

Do Cost Analysis

Stored procedures assume all the data processing overhead that was once dispersed among the clients, so a complex procedure that consumes a certain amount of processing overhead compounds the overhead for every connection associated with a procedure currently executing in the server. If you have not yet mastered the art of profiling your stored procedures and analyzing your queries, you should spend some time with the Profiler, as discussed in Chapter 18 before embarking on an extensive stored procedure development effort.

Another cost of stored procedures is the indirect cost of adopting a new style and philosophy of programming. To obtain the data from your client that you send to the stored procedure, you will likely have to change the way your client does business with the user. Bound controls, for example, are history in many cases (you don’t have them on the Internet), and so you might have to add code in some places where data-bound controls have been excised. The following Java code is a good example of how you would code the assignment of data in place of a data-bound control, especially in middle-tier solutions:

 String ProcParaml="Jeffrey"; String ProcParam2="Shapiro"; -or String ProcParaml=FNText.Text; String ProcParam2=LNText.Text;

Note 

I cannot think of a better word than excise to describe getting rid of data-bound controls in a client application. Honestly, they were good in the early days of Delphi and VB, when the database engine squatted on your local PC like a warthog trapped in mud. With SQL Server solutions, they truly are a thing of the past. Since I have been working exclusively with SQL Server, I have removed all of my data-bound controls from my applications.

Determine Error Checking Requirements

See the trigger plan discussed earlier for this section of the stored procedure plan. Error handling in stored procedures is also a lot more involved due to the code’s inclination to say something to the client. I have thus discussed error handling in stored procedures in more detail later in this chapter.

Determine Effects in the ClientTier

One of the positive effects of getting rid of SQL code in the client tier is that you’ll end up with clients that look a lot thinner than usual. Your code will also be cleaner and easier to document in the client. However, the downside is that the process of converting to a client/ server system and adopting stored procedures can be long and involved. For example, you might make extensive use of ADO.NET visual controls that need to have their dataset methods changed from SQL to stored procedure calls, and so on. I have simply dumped data-bound grids and the like and chosen to work only at the object level in ADO.NET, pulling back a result set from a stored procedure and then looping the data up into a dataset.

To me, creating a stored procedure to replace a data-bound ADO grid is like guzzling a Bud on a hot August afternoon on Miami Beach. You’ll likely have to spend a morning to code a complex procedure that returns the same data as the data-bound grid. But once you have tested the procedure in QA or whatever tool you use, seeing the data appear in a simple grid in the client is a wonderful feeling, knowing that all the client had to do was issue a single line of code to call the proc.