3 4
In Chapters 3 through 7, you used Query Analyzer to create, execute, and save Transact-SQL commands and batches of commands as scripts written using Transact-SQL language. When the scripts are executed, the commands within them are processed by SQL Server to display result sets, to administer SQL Server, and to manipulate data contained in a database. When the scripts are saved, they are stored in the file system and are commonly given a SQL extension. Alternatively, Transact-SQL scripts can be named and saved in SQL Server as stored procedures. You can then invoke stored procedures in a number of ways, such as through Query Analyzer, in order to process the Transact-SQL statements within them.
In previous chapters, you used a number of stored procedures (such as sp_help and sp_helpconstraint). These procedures are stored in the Master database and contain Transact-SQL commands that make it easy for you to view various properties of objects in the database.
Stored procedures provide performance benefits, a programming framework, and security features unavailable to Transact-SQL commands sent to the server for processing. Performance is enhanced through local storage (local to the database), precompiling the code, and caching. A programming framework is provided through common programming constructs such as input and output parameters and procedure reuse. Security features include encryption and privilege limits that keep users away from the underlying database structure while still enabling them to run stored procedures that act on the database.
Every time a Transact-SQL command (or batch of commands) is sent to the server for processing, the server must determine whether the sender has appropriate permissions to execute the commands and whether the commands are valid. Once the permissions and the syntax are verified, SQL Server builds an execution plan to process the request.
Stored procedures are more efficient in part because the procedure is stored in SQL Server when it is created. Therefore, the content in the procedure runs at the server when the stored procedure is executed. A complex Transact-SQL script contained in a stored procedure is called by a single Transact-SQL statement, rather than by sending hundreds of commands over the network.
Before a stored procedure is created, the command syntax is checked for accuracy. If no errors are returned, the procedure's name is stored in the SysObjects table and the procedure's text is stored in the SysComments table. The first time the stored procedure is run, an execution plan is created and the stored procedure is compiled. Subsequent processing of the compiled stored procedure is faster because SQL Server does not recheck command syntax, re-create an execution plan, or recompile the procedure. The cache is checked first for an execution plan before a new plan is created.
NOTE
Once a stored procedure is created, you can call it whenever it's needed. This feature provides modularity and encourages code reuse. Code reuse increases the maintainability of a database by insulating the database from changing business practices. If business rules change in an organization, a stored procedure can be modified to comply with the new business rules. All applications that call the stored procedure will then comply with the new business rules without direct modification.
Like other programming languages, stored procedures can accept input parameters, return output parameters, provide execution feedback in the form of status codes and descriptive text, and call other procedures. For example, a stored procedure can return a status code to a calling procedure so that the calling procedure performs an operation based on the code received.
Software developers can write sophisticated programs in a language such as C++; then, a special type of stored procedure called an extended stored procedure can be used to invoke the program from within SQL Server.
You should write a stored procedure to complete a single task. The more generic the stored procedure, the more useful it will be to many databases. For example, the sp_rename stored procedure changes the name of a user-created object, such as a table, a column, or a user-defined data type in the current database. Thus, you can use sp_rename to rename a table in one database or a table column in another database.
Another important feature of stored procedures is that they enhance security through isolation and encryption. Database users can be given permission to execute a stored procedure without being granted permissions to directly access the database objects on which the stored procedure operates. Additionally, a stored procedure can be encrypted when it is created or modified so that users are unable to read the Transact-SQL commands in the stored procedure. These security features insulate the database structure from the database user, which further ensures data integrity and database reliability.
There are five classes of stored procedures: system stored procedures, local stored procedures, temporary stored procedures, extended stored procedures, and remote stored procedures. There are other ways to categorize stored procedures, but this organization into five groups makes it easy to identify the stored procedure's location, purpose, and capability.
System stored procedures are stored in the Master database and are typically named with an sp_ prefix. They perform a wide variety of tasks to support SQL Server functions (such as catalog procedures) that support external application calls for data in the system tables, general system procedures for database administration, and security management functions. For example, you can view table privileges by using the sp_table_privileges catalog stored procedure. The following statement uses the sp_table_privileges system stored procedure to show privileges to the Stores table in the Pubs database:
USE Pubs GO EXECUTE sp_table_privileges Stores
A common database administration task is viewing information about current database users and processes. This step is an important one before a database is shut down. The following statement uses the sp_who system stored procedure to display all processes in use by the LAB1\Administrator user:
EXECUTE sp_who @loginame='LAB1\Administrator'
Database security is critical to most organizations that store private data in a database. The following statement uses the sp_validatelogins system stored procedure to show any orphaned Windows NT or Windows 2000 user and group accounts that no longer exist but still have entries in the SQL Server system tables:
EXECUTE sp_validatelogins
There are hundreds of system stored procedures included with SQL Server. For a complete list of system stored procedures, refer to "System Stored Procedures" in SQL Server Books Online. You will notice in the system stored procedures reference that some extended stored procedures are listed. Extended stored procedures are discussed later in this lesson.
Local stored procedures are usually stored in a user database and are typically designed to complete tasks in the database in which they reside. A local stored procedure might also be created to customize system stored procedure code. To create a custom task based on a system stored procedure, first copy the contents of a system stored procedure and save the new stored procedure as a local stored procedure. You will create local stored procedures for the BookShopDB database in Exercise 3.
A temporary stored procedure is similar to a local stored procedure, but it exists only until either the connection that created it is closed or SQL Server is shut down. The stored procedure is deleted at connection termination or at server shutdown, depending on the type of temporary stored procedure created. This volatility exists because temporary stored procedures are stored in the TempDB database. TempDB is re-created when the server is restarted; therefore, all objects within this database disappear upon shutdown. Temporary stored procedures are useful if you are accessing earlier versions of SQL Server that do not support the reuse of execution plans and if you don't want to store the task because the same task will be executed with many different parameter values.
There are three types of temporary stored procedures: local (also called private), global, and stored procedures created directly in TempDB. A local temporary stored procedure always begins with #, and a global temporary stored procedure always begins with ##. Lesson 2 explains how to create each type of temporary stored procedure. The execution scope of a local temporary procedure is limited to the connection that created it. All users who have connections to the database, however, can see the stored procedure in the Object Browser window of Query Analyzer. Because of its limited scope, there is no chance of name collision between other connections that are creating temporary stored procedures. To ensure uniqueness, SQL Server appends the name of a local temporary stored procedure with a series of underscore characters and a connection number unique to the connection. Privileges cannot be granted to other users for the local temporary stored procedure. When the connection that created the temporary stored procedure is closed, the procedure is deleted from TempDB.
Any connection to the database can execute a global temporary stored procedure. This type of procedure must have a unique name, because all connections can execute the procedure and, like all temporary stored procedures, it is created in TempDB. Permission to execute a global temporary stored procedure is automatically granted to the public role and cannot be changed. A global temporary stored procedure is almost as volatile as a local temporary stored procedure. This procedure type is removed when the connection used to create the procedure is closed and any connections currently executing the procedure have completed.
Temporary stored procedures created directly in TempDB are different than local and global temporary stored procedures in the following ways:
Because this procedure type is created directly in TempDB, it is important to fully qualify the database objects referenced by Transact-SQL commands in the code. For example, you must reference the Authors table, which is owned by dbo in the Pubs database, as pubs.dbo.authors.
An extended stored procedure uses an external program, compiled as a 32-bit dynamic link library (DLL), to expand the capabilities of a stored procedure. A number of system stored procedures are also classified as extended stored procedures. For example, the xp_sendmail program, which sends a message and a query result set attachment to the specified e-mail recipients, is both a system stored procedure and an extended stored procedure. Most extended stored procedures use the xp_ prefix as a naming convention. However, there are some extended stored procedures that use the sp_ prefix, and there are some system stored procedures that are not extended and use the xp_ prefix. Therefore, you cannot depend on naming conventions to identify system stored procedures and extended stored procedures.
Use the OBJECTPROPERTY function to determine whether a stored procedure is extended or not. OBJECTPROPERTY returns a value of 1 for IsExtendedProc, indicating an extended stored procedure, or returns a value of 0, indicating a stored procedure that is not extended. The following examples demonstrate that sp_prepare is an extended stored procedure and that xp_logininfo is not an extended stored procedure:
USE Master --an extended stored procedure that uses an sp_ prefix. SELECT OBJECTPROPERTY(object_id('sp_prepare'), 'IsExtendedProc')
This example returns a value of 1.
USE Master --a stored procedure that is not extended but uses an xp_ prefix SELECT OBJECTPROPERTY(object_id('xp_logininfo'), 'IsExtendedProc')
This example returns a value of 0.
As the name suggests, a remote stored procedure executes a stored procedure on a remote SQL Server installation. Remote stored procedures remain for backward compatibility but have been replaced by distributed queries. For information about distributed queries, refer to Chapter 7, "Managing and Manipulating Data."
In this exercise, you will view a number of system stored procedures contained in the Master database. To perform this exercise, you should be logged into your Windows 2000 Server computer as Administrator.
The Object Browser window displays a hierarchical tree of database objects.
A list of object types appears. Notice the Stored Procedures and Extended Procedures nodes.
A list of stored procedures and extended stored procedures in the Master database appears.
The Parameters and Dependencies nodes appear.
Notice that there are two parameters defined for this procedure: @RETURN_VALUE and @loginame. @RETURN_VALUE is built-in and is used to supply return codes. All stored procedures contain the @RETURN_VALUE parameter; @loginame is an input parameter. The second example code listed under the System Stored Procedures section in this lesson sets the @loginame equal to LAB1\Administrator.
Notice that there is a single dependency for this stored procedure: the dbo.sysprocesses table. This is a system table stored in the Master database. The sp_who system stored procedure queries the SysProcesses table and displays parts of this table in its result set. You can find this table by expanding the System Tables node.
Notice that right above the Extended Procedures node, there are three stored procedures beginning with the xp_ prefix. These are not extended stored procedures. You can verify this fact by running the OBJECTPROPERTY function described in the lesson against them.
As you scroll through the objects contained in this node, notice that both system stored procedures and extended stored procedures exist. The majority of procedures under this node are extended stored procedures.
The context menu for this object appears.
The Options dialog box appears.
This option will display the stored procedure name and its create date near the top of the text contained in the procedure.
The context menu for this object appears.
The dbo.sp_who stored procedure appears in the Query window.
Notice that the CREATE PROCEDURE keywords appear near the top of the file. In the next lesson, you will learn more about creating stored procedures using Transact-SQL. Notice that batches of commands are specified in the stored procedure text. As you examine the text, you will see that the @loginame input parameter appears in a number of places. Notice also that the SysProcesses table is queried a number of times in the stored procedure using its fully qualified name, master.dbo.sysprocesses.
Don't worry about not understanding everything in this stored procedure. In Lessons 2 and 3, you will learn more about how to manage and program stored procedures.
A new query window appears in the right pane.
sp_helptext [master.dbo.sp_who]
This code uses the sp_helptext system stored procedure to show the contents of the sp_who system stored procedure contained in the Master database. It is not necessary to fully qualify the name of the stored procedure, but it is a good practice because it guarantees that you will open the object that you are interested in viewing.
The Grids tab of the Results pane displays the contents of the sp_who system stored procedure.
Stored procedures contain Transact-SQL commands that are stored in a database and are typically designed to run a single task. Stored procedures provide performance gains, programming capabilities, and security features not available when using individual Transact-SQL commands or batches. Storing the procedure in a database, checking the syntax when the procedure is created, and creating a reusable execution plan the first time the stored procedure is executed will enhance performance. Stored procedures support parameters and return codes. Because stored procedures are modular, they provide a layer of insulation between changing business practices and the underlying database. The programming capabilities of stored procedures are nearly unlimited because of support for extended stored procedures. From a security perspective, users can be granted the right to execute a stored procedure without being granted the right to manipulate the database outside the stored procedure.
There are five general categories of stored procedures: system, local, temporary, extended, and remote. SQL Server includes hundreds of system stored procedures and extended stored procedures to perform tasks such as system administration. Local stored procedures are created in a user database and are typically used to complete tasks specific to the database in which they are created. Temporary stored procedures look and act like any of the other categories of stored procedures, but they are volatile and disappear upon the termination of a user connection (or when the server is shut down). Remote stored procedures are available for backward compatibility but have been replaced by distributed queries.