Lesson 1:Introduction to Stored Procedures

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.


After this lesson, you will be able to:

  • Describe the purpose and reasons for using stored procedures.
  • Explain how SQL Server processes a stored procedure.
  • Determine when to use stored procedures to complete SQL Server tasks.

Estimated Lesson time: 30 minutes


Purpose and Advantages of Stored Procedures

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.

Performance

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


The relative performance boost provided by placing stored procedure execution plans in the procedure cache is reduced because execution plans for all SQL statements are now stored in the procedure cache. A Transact-SQL statement will attempt to use an existing execution plan if possible.

Programming Framework

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.

Security

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.

Categories of Stored Procedures

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

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

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.

Temporary Stored Procedures

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:

  • You can configure permissions for them.
  • They exist even after the connection used to create them is terminated.
  • They aren't removed until SQL Server is shut down.

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.

Extended Stored Procedures

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.

Remote Stored Procedures

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."

Exercise 1:  Exploring Stored Procedures

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.

To view system stored procedures in the Master database

  1. Open Query Analyzer and connect to your local server.
  2. Open the Object Browser window if it is not already displayed.

The Object Browser window displays a hierarchical tree of database objects.

  1. Expand the master node.

A list of object types appears. Notice the Stored Procedures and Extended Procedures nodes.

  1. Expand the Stored Procedures node.

A list of stored procedures and extended stored procedures in the Master database appears.

  1. Review the names of the listed procedures. Notice that dbo owns all of the procedures.
  2. How can you tell the difference between a system stored procedure and an extended stored procedure from the list of procedures that appear below the Stored Procedures node?
  3. Expand the dbo.sp_who system stored procedure.

The Parameters and Dependencies nodes appear.

  1. Expand the Parameters node.

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.

  1. Expand the Dependencies node.

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.

  1. If you examined the dbo.sysprocesses table in the previous step, return to the sp_who system stored procedure in the Object Browser.
  2. Scroll down in the Object Browser until you see the Extended Procedures 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.

  1. Expand the Extended Procedures node.

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.

  1. Keep Query Analyzer open for the next exercise.

To use two methods for viewing the contents of a stored procedure

  1. Click once on the dbo.sp_who object.
  2. Right-click dbo.sp_who.

The context menu for this object appears.

  1. Click Scripting Options.

The Options dialog box appears.

  1. Click the Include Descriptive Headers In The Script check box and click OK.

This option will display the stored procedure name and its create date near the top of the text contained in the procedure.

  1. Right-click dbo.sp_who.

The context menu for this object appears.

  1. Point to Script Object To New Window As and click Create.

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.

  1. Click New Query on the toolbar or press CTRL + N.

A new query window appears in the right pane.

  1. In the Editor pane of the Query window, enter the following Transact-SQL code:
 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.

  1. Execute the code.

The Grids tab of the Results pane displays the contents of the sp_who system stored procedure.

Lesson Summary

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.



Microsoft Press Staff - MCSE. Microsoft SQL Server 2000 Database Design and Implementation Training Kit
MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)
ISBN: 073561248X
EAN: 2147483647
Year: 2001
Pages: 97

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net