Chapter 11 -- Batches, Stored Procedures, and Functions

Chapter 11

In this chapter, we'll use Transact-SQL for more than interactive queries. When you send a query to the server, you're sending a command batch to SQL Server. But we can do even more. For example, we can wrap up commands in a module that can be stored and cached at the server for later reuse. Modules that contain data modification operations can be created as stored procedures; modules that return a value or a result set without affecting other data in your database can be created as user-defined functions.

I'll describe the creation, use, and internal storage of stored procedures and user-defined functions. The ANSI SQL Standard refers to both stored procedures and user-defined functions as "routines"; I'll use that term when I need to refer to both these types of objects together. SQL Server allows us to create another type of stored module that is automatically executed when a request is made to modify data; this type of object is called a trigger. However, the SQL Standard does not consider triggers in its definition of "routines," so I won't either. I'll cover the details of trigger creation and use in Chapter 12.