What Is a Stored Procedure? | Microsoft SQL Server 2000 Administrators Companion

3 4

A stored procedure is a collection of T-SQL statements that SQL Server compiles into a single execution plan. This plan is stored in the procedure cache area of memory when the stored procedure is first executed so that the plan can be used repeatedly; SQL Server does not have to recompile it every time the stored procedure is run. T-SQL stored procedures are similar to procedures in other programming languages in that they can accept input parameters, return output values as parameters, or return success or failure status messages. All of the statements in the procedure are processed when the procedure is called. Stored procedures are used to group the T-SQL statements and any associated logic needed to perform a task. Since they are stored as a procedural unit, stored procedures can be used to allow different users to repeat the same task consistently, even across multiple applications. Stored procedures also provide a single point of control for a task, which helps to ensure that any business rules are consistently and correctly enforced.

Your application can communicate with SQL Server in two ways—you can code the application to send the T-SQL statements from the client to SQL Server, or you can create stored procedures, which are stored and run at the server. If you send your T-SQL statements from the client application to the server, the statements will be sent across the network and recompiled by SQL Server each time they are run. If you use stored procedures, you can execute the stored procedures by calling them from your application with one statement. As mentioned, the first time a stored procedure is executed, it is compiled and its execution plan is created and stored in memory. SQL Server will then use this execution plan when the procedure is called again, without recompiling it. Therefore, when several TSQL statements are needed to perform a task or when a statement is processed frequently, using a stored procedure can reduce network traffic and can be more efficient and faster than sending each statement over the network from the client to the server.

Stored procedures can improve performance in other ways. For example, using stored procedures for testing server conditions can improve performance by reducing the amount of data that must be passed between the client and the server and by reducing the amount of processing done on the client. To test for a condition from within a stored procedure, you program conditional statements (such as the IF and WHILE constructs, covered in Chapter 20) into the stored procedure. The logic for this test will be handled at the server through the stored procedure, so you don't have to code that logic into the application itself, and the server does not have to return intermediate results to the client to test the condition. You can also call stored procedures from scripts, batches, and interactive command lines by using the T-SQL statements shown in the examples later in this chapter.

Stored procedures also provide easy database access for users. Users can access the database without having to know the architectural details of the tables and without directly accessing table data—they simply execute the procedures that perform the required tasks. In this way, stored procedures help to ensure that the business rules are protected.

Stored procedures can accept input parameters, use local variables, and return data. Stored procedures can return data by using output parameters, return codes, result sets from SELECT statements, or global cursors. You'll see examples of these techniques (except using global cursors) in later sections.

MORE INFO
You can find information about using cursors and global cursors by searching for "Cursors" in the Search tab of Books Online and selecting the topics "Cursors" (in the Transact-SQL Reference) and "DECLARE CURSOR (T-SQL)."

There are three types of stored procedures: system stored procedures, extended stored procedures, and simple user-defined stored procedures. System stored procedures are supplied by SQL Server and have the prefix sp_. They are used to manage SQL Server and to display information about databases and users. System stored procedures were introduced in Chapter 13. Extended stored procedures are dynamic-link libraries (DLLs) that SQL Server can dynamically load and execute. They are generally written in C or C++, and they execute routines external to SQL Server. Extended stored procedures have the prefix xp_. Simple user-defined stored procedures are created by the user and customized to perform whatever task the user wants.

NOTE
You should not use the sp_ prefix when you create simple user-defined stored procedures. When SQL Server encounters a stored procedure that has the sp_ prefix, it first looks for that stored procedure in the master database. So if you create a stored procedure named sp_myproc in the MyDB database, for example, SQL Server will look for the procedure in the master database before it looks in any user databases. It would be more efficient to name the procedure simply myproc.

In this chapter, we'll focus on simple user-defined stored procedures. Before we begin learning about these procedures, however, we'll briefly go over some basic facts about extended stored procedures. Extended stored procedures allow great flexibility and extensibility in the SQL Server environment. They allow you to create your own external routines in C, C++, or other programming languages. Extended stored procedures are executed in the same manner as the other two types of stored procedures. You can pass parameters to extended stored procedures, like you can to the other types of stored procedures, and they can return result sets, status messages, or both.

As mentioned, extended stored procedures are DLLs that SQL Server dynamically loads and executes. They run directly in the address space of SQL Server, and you program them by using the SQL Server Open Data Services API.

You write extended stored procedures outside SQL Server. Once you complete an extended stored procedure, you register it in SQL Server, either by using T-SQL commands or via Enterprise Manager.

MORE INFO
For additional information about and examples of extended procedures, look in SQL Server Books Online.