Introducing Stored Procedures

As a developer, you're likely to hear quite a bit about the wisdom (or lack thereof) of using stored procedures. Some of the naysayers come to the argument with a personal agenda or simply fear of something they don't really understand well enough to endorse. I've been working with SQL Server from the beginning, and I (like 90% of the people I poll in my conference sessions) feel that stored procedures can make the difference between an amateur and a professional application.

Stored procedures are just thatthey're scripts of executable code that are stored on SQL Server for later execution. No, unlike Visual Basic .NET or C# programs, they're not precompiled and salted away somewhere in binary form. Stored procedures (at least, the T-SQL variety) are simply saved to the user or system database, where they can be called up, compiled, and executed when needed. Let's step through the process of creating and executing a stored procedure.

1.	The stored procedure is created in an ordinary text editor (like Visual Notepad), Visual Studio, or SQL Server Management Studio's query editor window. These tools can help author any queries or action commands incorporated in the procedureI'll show you how in Chapter 5.
2.	When "saved" by Visual Studio or executed by SQL Server Management Studio, the CREATE PROCEDURE statement tells SQL Server to record the stored procedure to the database catalogsonce it passes syntax and initial object "sanity" checks.
3.	When the stored procedure is executed, SQL Server retrieves the stored procedure from the database catalogs on disk, compiles the procedure, and makes sure that the objects referenced are accessible, given the rights of the user executing the procedure.
4.	The compiled stored procedure is passed to the query optimizer that, given the input parameters passed by the code executing the procedure, decides how to perform the operations requested. This becomes the query plan (as I discuss later in this chapter).
5.	Using the query plan, SQL Server executes the compiled stored procedure and saves it to the common data/procedure cache. Any resultsets are returned to the calling application.
6.	When the space occupied by the stored procedure in the data/procedure cache is needed, it's overlaid, and the query plan and executable code are lost.

A performance benefit can be achieved when a stored procedure is still in the cache when an application calls it again. In this case, the cached plan and procedure executable are reusedeven if the input parameters used would generate an entirely different query plan. This means that a stored procedure might not run any faster than an ad hoc query (one submitted by your application as raw T-SQL). That's because the process for executing an ad hoc query is basically the same. If there is a query plan and compiled executable in the cache generated by a previous execution of a (very) similar ad hoc query, it's reused.

So why bother with stored procedures? Well, stored procedures move the T-SQL out of your applications and into a central location where they can be managed, tuned, corrected, and protected by the DBA and the entire staff. It means when schemas or business rules change, this shared code can be morphed without having to redeploy your application (at least, part of the time). Consider that a stored procedure can be granted rights to tables that the developer might not (or should not) have rights to access. It can encapsulate operations so that complex update or query operations can be made with ease without having to worry about the underlying details.

Throughout the book, I show how to create, edit, test, and interactively debug stored procedures using Visual Studio's Server Explorer (in Chapter 5) and with ADO.NET (in Chapters 1012okay, in most chapters). In SQL Server 2005, it's possible to write stored procedures and other server-side executables in Visual Basic .NET or C#. I devote Chapter 13 to this new paradigm.