Creating and Dropping Stored Procedures

Stored procedures are created using the CREATE PROCEDURE statement or the equivalent statement CREATE PROC. When a stored procedure is created, its properties are stored in the sysobjects system table, and its definition (all the statements it contains) in the syscomments system table. A stored procedure is stored in the current database; therefore, if you want to create a stored procedure in other databases, you have to make the other database the current one before creating it (using the USE statement ).

After a stored procedure is created, you can view its parameters and definition using the sp_helptext system stored procedure. You can view its properties using sp_help.

In Listing 8.5, you can see an example of the syntax used to create a stored procedure. Followed by the creation, it shows the retrieval of the stored procedure's properties, using sp_help, and then its code, using sp_helptext.

Code Listing 8.5. Creating a Stored Procedure and Retrieving Its Properties and Code

					
USE Northwind
GO

CREATE PROC dbo.getcurrenttime
AS
SELECT CURRENT_TIMESTAMP
GO
EXEC sp_help 'getcurrenttime'
EXEC sp_helptext 'getcurrenttime'
GO

Name              Owner   Type                Created_datetime
----------------- ------- ------------------- -------------------------
getcurrenttime    dbo     stored procedure    2000-09-18 01:35:06.257


Text
----------------------------------
CREATE PROC getcurrenttime
AS
SELECT CURRENT_TIMESTAMP

There are three steps that SQL Server performs with stored procedures: parsing, name resolution, and optimization.

SQL Server parses a stored procedure when it is created to check for correct syntax. Then, the stored procedure's information is stored in sysobjects and syscomments.

The first time the stored procedure is executed, SQL Server checks that all the objects it references exist. This is a feature of SQL Server called deferred name resolution, which allows you to create stored procedures that reference objects that haven't been created yet. This is why this step is performed the first time the stored procedure is executed, not when it is created.

In the last step, SQL Server finds an optimized execution plan, looking for the best way to execute each statement inside the stored procedure. Then, an optimized execution plan is generated and stored in the procedure cache, which is part of the memory that SQL Server allocates for its use (the other part of the memory, the data cache, is used to store the data pages that SQL Server manipulates).

Figure 8.1 shows this three-step process (parse, name resolution, and optimization).

The execution plan of a stored procedure will remain in memory until SQL Server is stopped or when SQL Server needs the memory allocated for the plan. Therefore, if the procedure cache becomes full, stored plans are dropped to make space for new ones.

After the execution plan is created and stored in the procedure cache (memory), any time you execute the stored procedure, SQL Server just needs to reuse the plan to manipulate the data. SQL Server shows this cache information if you query the syscacheobjects system table. Be aware that syscacheobjects is a virtual table, not a real one. The only purpose of this virtual table is to provide support for internal procedures and DBCC commands, and the table is filled automatically with data when you use it. Specifically, you can retrieve information about the procedure cache by querying this virtual table (master.dbo.syscacheobjects).

The process of generating a good access plan involves evaluating many factors, such as indexes and data in tables. This is one of the reasons you should have good indexes on tables and views referenced by stored procedures, and also keep statistics up to date, which is a database option that is set by default when you create a database.

In SQL Server 6.5 and earlier, the only way to create an access plan was by using stored procedures. In version 7.0 and later, the query processor can store execution plans in the procedure cache for all Transact-SQL statements (including ad hoc queries). When reexecuting a Transact-SQL statement, if the query processor detects that it can reuse the plan, it takes it from the procedure cache, optimizing the execution time of the whole statement.

A feature of stored procedures, as mentioned earlier, is that they can be set to execute automatically when the SQL Server service is started. Because they won't have any interaction with any application, they can't have any input parameters. The stored procedure must be created by the system administrator in the master database, and then the system stored procedure sp_procoption must be used to set it to execute when the SQL Server service is started.

For example, suppose that you want to be able to know every time the SQL Server service was started. To accomplish this, you can create a table in master to store the date and time when the SQL Server service has been started, and then create a stored procedure that inserts a row in this table with the current date. Finally, set this stored procedure to execute automatically whenever SQL Server is started. Listing 8.6 shows the code needed to achieve these steps.

Code Listing 8.6. Using the sp_procoption System Stored Procedure

					
USE Master
GO

CREATE TABLE dbo.Sqlstatus (
lasttime DATETIME
)
GO

CREATE PROC dbo.insertsqlstatus
AS
INSERT Sqlstatus (lasttime)
VALUES (CURRENT_TIMESTAMP)
GO

EXEC sp_procoption 'insertsqlstatus','startup','true'

To test this example, follow the next steps:

To verify that a stored procedure that was configured to execute automatically was successfully executed, you can check the SQL Server error log. The error log will show the following message to indicate that the stored procedure was executed successfully:

Launched startup procedure 'name_of_the_stored_procedure'

Some statements can't be included in a stored procedure's code. These statements are CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW.

Stored procedures can be createdusing the WITH ENCRYPTION option, which encrypts the definition in the syscomments system table; therefore, nobody can read the definition. If you try to see the code of a stored procedure (using sp_helptext or any other method) and it has been encrypted, you will get this error

The object comments have been encrypted

Be cautious when you encrypt a stored procedure's definition, because you won't be able to display it again unless you keep the original source code. Therefore, if you need to modify the definition of a stored procedure that was created using the WITH ENCRYPTION option, you must use the original source code. It is always a good idea to keep a copy of the original scripts that you used to generate the database schema.

Listing 8.7 creates the getcurrentuser stored procedure using the WITH ENCRYPTION option, and then tries to show the code of the stored procedure using sp_helptext, without success.

Code Listing 8.7. Creation of a Stored Procedure Using the WITH ENCRYPTION Option

					
USE Northwind
GO

CREATE PROC dbo.getcurrentuser
WITH ENCRYPTION
AS
SELECT USER
GO

sp_helptext 'getcurrentuser'

The object comments have been encrypted.