Executes the compiled query plan and returns the data to the client

Query Plan

5. Executes the compiled query plan and returns the data to the client

Once the query plan for a procedure is cached, it will remain in cache until it is

http://www.itknowledge.com/reference/standard/1576101495/ch06/196-198.html (1 of 3) [1/27/2000 6:16:33 PM]

Go!

Keyword

---Go!

pushed out by other, more active procedures. If a procedure’s query plan is pushed out of the cache on SQL Server, it will be recompiled and a new query plan created the next time it is called. If the procedure is in cache, the user requesting data through a stored procedure must have the parameters used in the procedure checked and then have the plan reexecuted to return data. This reduces the amount of overhead on the server for the process of returning data to the clients. In many situations where a standard set of procedures is used and constantly cached, the users will see marked improvement over raw SQL code execution.

Stored procedures also enhance performance with regard to object references.

In raw SQL, every time a query is executed, each object reference is checked when it is passed to the server. With stored procedures, the objects are checked when compiled, and stored in the query plan. Each subsequent call to the stored procedure does not trigger the object reference check on the server, thus reducing overhead.

In addition, permissions are handled differently with stored procedures. When a stored procedure is written, the security context of the author is used. Access to all objects is based on the author’s permissions. Once the stored procedure is created, anyone calling the procedure assumes the permissions of the author inside the stored procedure. This way, the users of a stored procedure can be granted permission to run a procedure, but not to do anything else to the data.

Users are not able to see or change the code within a stored procedure—so the underlying objects and data are protected—and users are allowed to perform only the tasks specified. Stored procedures can greatly reduce the occurrence of security problems on your SQL server.

Parameters

The maximum number of parameters you can use in a stored procedure is 255.

I have never written a stored procedure that even came close to this limit, but I imagine someone will test this at some point. When possible, I limit the

number of parameters by using table lookups internally in the stored procedure to get the data I need. This reduces the amount of parameter traffic on the network and keeps the client application as “thin” as possible. Parameters are data type-specific and should be defined to the correct size and precision as needed inside the stored procedure. You can use the CONVERT() function to change any data types once they are passed into a stored procedure.

You also can use output parameters with stored procedures. These parameters are passed into the procedure marked as output parameters, modified internally within the stored procedure, and returned in the modified state to the client.

This can be useful with complex client applications that require multiple return values from a stored procedure as well as with result sets.

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch06/196-198.html (2 of 3) [1/27/2000 6:16:33 PM]

http://www.itknowledge.com/reference/standard/1576101495/ch06/196-198.html (3 of 3) [1/27/2000 6:16:33 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Variables

Another great feature of stored procedures is the ability to use local variables and access-to-server global variables. You can create as many local variables as needed with stored procedures, providing you have enough memory on the server set aside for Microsoft SQL Server. Local variables are designated by the @ sign, preceding the variable name. You must declare all variables before they are referenced and use data type-specific declarations so that Microsoft SQL Server knows how to use the variables.

To be consistent, I typically use the tmp lowercase prefix when naming my variables. With this method, I can distinguish easily between variables and column names. For a list of global variables you can access from within your SQL code, see Table 6.2. Note that this is a partial list of variables. See Microsoft SQL Server Books Online for complete information on variables.

Global variables have an @@ prefix to distinguish them from local variables.

Table 6.2 Global variables available on Microsoft SQL Server.

Variable Use Or Value

@@CONNECTIONS Contains the number of logins since SQL Server was last started.

@@CPU_BUSY Holds the amount of time the server has spent executing SQL statements since the last time SQL Server was started. (Data is in ticks, which are one three-hundredth of a second, or 3.33 milliseconds.)

http://www.itknowledge.com/reference/standard/1576101495/ch06/198-200.html (1 of 3) [1/27/2000 6:16:35 PM]

Go!

Keyword

---Go!

@@ERROR Holds the return code or status of the last SQL statement executed on SQL Server. This variable is maintained on a per-connection basis. (A value of 0 means success.)

@@FETCH_STATUS Contains the result of a cursor’s FETCH command. This will be 0 if the FETCH is successful, -1 if the FETCH failed or the row was beyond the record set for the cursor, and -2 if the row fetched is missing.

@@IDENTITY Holds the value of the last identity value generated by an INSERT statement. Identity values are generated automatically by SQL Server for each table that has an identity column and will be unique for each record in that table.

This value is maintained on a connection-by-connection basis.

@@IDLE Specifies the amount of time, in ticks, that SQL Server has been idle since it was last started.

@@IO_BUSY Contains the time, in ticks, that SQL Server has spent performing input and output operations since it was last started.

@@MAX_CONNECTIONS Holds the maximum count of simultaneous connections that can be made with the server at one time. This is not the amount of client

licenses that you have purchased. One client can acquire more than one connection in a

multithreaded application.

@@NESTLEVEL Holds the current count of how many levels you are nested within your stored procedures. The maximum nesting level is 16. If you exceed the maximum level, your transaction will be

terminated.

@@PROCID Holds the stored procedure ID of the current stored procedure.

@@ROWCOUNT Specifies the number of rows affected by the immediately previous statement. Set to 0 (zero) for statements such as IF or control-of-flow statements. Caution should be taken when relying on this variable for the number of rows re-turned by queries. Local variable assignments can set this value to another number and

mislead you.

@@SPID Holds the server process ID number of the current procedure. This value can be used for looking up information about a stored procedure in the SYSPROCESSES system table.

http://www.itknowledge.com/reference/standard/1576101495/ch06/198-200.html (2 of 3) [1/27/2000 6:16:35 PM]

@@TRANCOUNT Specifies the number of currently active transactions for the current user. This value is maintained on a connection-by-connection basis.

Many of the variables listed in Table 6.2 can be combined with queries against the system tables in Microsoft SQL Server to create an intelligent set of stored procedures. I often find that to create a stored procedure to perform a highly complex task, using variables can save me hours of programming. Spend some time in Microsoft SQL Server Books Online and view some of the sample code to become more familiar with how local and global variables can be used in your code.

You cannot create a global variable on Microsoft SQL Server

programmatically. We have discussed using tables on your server to store parameters or even runtime variables that must be shared between client connections to simulate server-based global variables (see the section on application-level parameters in Chapter 3).

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch06/198-200.html (3 of 3) [1/27/2000 6:16:35 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

NT Server Registry

When you require server-based global variables that are static with regard to the server operation, you have one other option: the Windows NT Registry on the server itself. This location is a static repository many client applications use on each local machine for startup and runtime parameters. I recommend this option only for experienced programmers with a good knowledge of the Windows NT Registry and how it works.

You can make calls to the Windows NT Registry by using external stored procedures available with Microsoft SQL Server (see Table 6.3). If you use the Windows NT Registry on your server, you should adopt a very strict standard of writing, reading, and deleting these values so that you do not leave a lot of stray entries floating around in the Registry.

Table 6.3 External procedures used to access the Windows NT Registry.

Name Function

XP_REGREAD Reads a key value or data from the Registry.

XP_REGWRITE Writes a key value or data to the Registry.

XP_REGDELETEVALUE Removes a key from the Registry.

Note that the procedures listed in Table 6.3 are not documented in Microsoft SQL Server Books Online. You can pick up bits and pieces and the syntax from

looking up XP_REGWRITE and finding a related topic with some examples.

http://www.itknowledge.com/reference/standard/1576101495/ch06/200-203.html (1 of 3) [1/27/2000 6:16:36 PM]

Go!

Keyword

---Go!

As with any other application, adopting a standard of using a specific key value structure is a good idea when using these procedures. Typically, the directory structure that falls under the MSSQLSERVER key in the Registry is where you should place all your subkeys. Use well-thought-out, descriptive names for your keys. This makes finding your custom Registry entries with an application like REGEDT32.EXE easier. See Listing 6.2 for an example of how to add a key, read the new key, and remove the key within an SQL script or stored procedure.

Listing 6.2 External procedures for accessing the Windows NT Registry.

/* Sample Registry script */

SET NOCOUNT ON

/* Local variables */

DECLARE @tmpAuthorID VARCHAR(11) DECLARE @tmpMichiganID VARCHAR(11) ‘SOFTWARE\Microsoft\MSSQLServer\Global_Keys’, ‘Michigan’,

‘REG_SZ’, @tmpAuthorID

/* Return the Registry value */

EXEC master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’, ‘SOFTWARE\Microsoft\MSSQLServer\Global_Keys’, ‘Michigan’,

@param = @tmpMichiganID OUTPUT /* Display the Registry value */

PRINT @tmpMichiganID

/* Remove the key */

EXEC master.dbo.xp_regdeletevalue ‘HKEY_LOCAL_MACHINE’, ‘SOFTWARE\Microsoft\MSSQLServer\Global_Keys’,

‘Michigan’

Notice that each EXEC line in Listing 6.2 references the stored procedure with the database, owner, and name of the stored procedure separated by periods. This allows you to be in the Pubs database and run a procedure stored in another database without changing the current active database.

Each procedure lists the sample syntax to perform the basic tasks of getting things into and out of the Registry. You can use these samples as a template and modify

http://www.itknowledge.com/reference/standard/1576101495/ch06/200-203.html (2 of 3) [1/27/2000 6:16:36 PM]

them for your environment by replacing the key values and the data types of the keys on a case-by-case basis. The listing is provided only to illustrate the use of these stored procedures to create pseudo-global variables on your server. This method can be used to store many things, such as server-error state or the steps of a long-running process.

The only real drawback to using the Registry in this manner is that you are

creating additional disk I/O for each call. Also keep in mind that these procedures are not documented well at all in Microsoft SQL Server Books Online.

Maintenance

Stored procedures allow you to centralize your code management. You can easily maintain and/or debug your SQL code on the server and be sure that all the clients that call the stored procedures are not going to run into SQL errors or introduce problems in the server environment. I typically print out each of my stored

procedures and keep a hard copy around for developers to use when writing client applications. I can change to a stored procedure in one location to provide

additional features to all the clients that use my stored procedures.

Periodically you should recompile each of your stored procedures to ensure that the underlying data structure and query plans are all up to date and error-free. You cannot recompile or change a procedure that is in use.

When performing maintenance on your procedures, you might occasionally see an error message saying that you cannot change a stored procedure that is in use. Do not be alarmed—this is normal. To solve this problem, make sure you are not the one with the procedure loaded in another window, and try to recompile the procedure again. Users do not have to disconnect for you to replace or rebuild a stored procedure. However, they must not be accessing it at the time of recompile.

Remember that users load the procedure into cache the first time it is called and use the cached information from that point on until it is purged from cache or recompiled with the RECOMPILE option.

Finally, as far as procedure maintenance goes, I usually create a master script file containing all my procedures for a database, and then back it up on tape or disk.

This allows me to re-create all my stored procedures in a single step on the same server, or on another server should the need arise. The only prerequisite to running the master script is that all the underlying dependent objects that are referenced in the procedures exist prior to running the script.

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch06/200-203.html (3 of 3) [1/27/2000 6:16:36 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

In document Microsoft SQL Server Black Book pdf (Page 186-195)