SQL Server™ 2005 Bible

Chapter 21. Developing Stored Procedures

In This Chapter

• Creating and managing stored procedures

• Passing data to and from stored procedures

• Using stored procedures within ad hoc queries

• Executing stored procedures on linked SQL servers

• An order-entry stored-procedure scenario

The primary purpose of client/server development is to move the processing as close to the data as possible. Moving data processing from a client application to the server reduces network traffic and improves both performance and data integrity.

One of the most popular methods of moving the processing closer to the data is developing stored procedures, sometimes called procs, or sprocs. Stored procedures aren't mysterious. All the features of T-SQL queries and batches are in full force. In the same way that a view is a SQL query saved under a view name, a stored procedure is a batch that has been stored with a name so it can be pre-compiled.

Within a client-server database project, code can be created in any of several places. One of the distinctive differences about the various places is how close to the data the code is executed. On the continuum between "close to the data" and "separate from the data," illustrated in Figure 21-1, stored procedures mix the benefits of server-side code with custom programmability.

Figure 21-1. On the continuum of processing, the closer the processing is to the data, the better.

As server-side ...