| Previous | Table of Contents | Next |
Stored procedures are batches that SQL Server stores in the database and then executes by name. They provide a number of benefits, including enhanced control of data, straightforward access to complex data operations, and improved performance. In some applications, a DBA may decide that procedures should be the only interface to the data.
Today you will learn how to write and manage stored procedures and how the server uses them. Tomorrow we will look at some advanced issues about stored procedures. Here are the topics to be discussed today:
As you’ve worked through this book, you’ve learned to use a number of stored procedures to get information about system objects or about the status of SQL Server. Some of those stored procedures include:
In addition to all of these procedures that simply select data, there are several system stored procedures used by administrators to manage users (sp_adduser, sp_dropuser), logins (sp_addlogin, sp_droplogin), databases (sp_changedbowner, sp_dboption), and tables (sp_bindrule, sp_rename). These stored procedures provide a simple and carefully managed point of entry to sensitive tables, such as the system tables that maintain all the information about the objects we use.
The reporting procedures provide several benefits. First, they enable us to extract useful information from the server even though we don’t really understand the structure of the tables themselves. Look at sp_help. It draws information from sysobjects, syscolumns, systypes, sysindexes, and master..spt_values.
Second, reporting procedures provide useful modularity. sp_help contains about 350 lines of SQL code, and it calls three other procedures (sp_helpconstraint [600 lines], sp_helpindex [350 lines], and sp_objectsegment [30 lines]). So if you wanted to write the code by hand to duplicate the functionality of sp_help, you would need to compose about 1300 lines of SQL. The annoyance of finding and managing that much SQL code in your client program would drive you crazy; writing similar amounts of code specific to your application would cost a fortune in programming time.
Third, procedures help network performance. The network impact of sending 1300 lines of SQL every time you want a simple query result would be devastating. Many stored procedures are run constantly, all day long, by dozens or hundreds of users.
The last benefit comes in security and data integrity. SQL Server does not normally allow any direct modification of the system tables. For example, you can’t execute this statement to change the name of a column in the authors table:
update syscolumns set name = “lastname” where id = object_id(“authors”) and name = “au_lname”
Instead, the only way to rename a column is to use the sp_rename stored procedure:
sp_rename “authors.au_lname”, “lastname”
The code we just wrote to do the update seems pretty simple. The sp_rename procedure is over 1200 lines of SQL. Its UPDATE statement looks pretty much the same as ours:
UPDATE
syscolumns
set
name = @newname
where
id = @Tab_id
and name = @UnqualOldName
So what did the procedure do in the other 1193 lines? It checked to make certain that every aspect of the update was correct. Does the old column exist? Is the new name a valid name for a column? Is the new name unique in the table? Is this user permitted to change it? Every possible error gets a unique error message.
By carefully controlling the modifications to the system tables, SQL Server avoids data corruption that might cause the system to fail and data to be lost. The data managed by your application is equally important and probably merits the same level of care. This chapter shows you how to write stored procedures so that you can introduce similar data control in your database.
| Previous | Table of Contents | Next |