Creating a Stored Procedure

You can create stored procedures using the CREATE PROCEDURE Transact-SQL statement. Before creating a stored procedure, consider that:

• CREATE PROCEDURE statements cannot be combined with other SQL statements in a single batch.
• Permission to create stored procedures defaults to the database owner, who can transfer it to other users.
• Stored procedures are database objects, and their names must follow the rules for identifiers.
• You can create a stored procedure only in the current database.

When creating a stored procedure, you should specify:

• Any input parameters and output parameters to the calling procedure or batch.
• The programming statements that perform operations in the database, including calling other procedures.
• The status value returned to the calling procedure or batch to indicate success or failure (and the reason for failure).

System Stored Procedures

Many of your administrative activities in Microsoft® SQL Server™ are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for stored procedures beginning with sp_ in this order:

1. Look for the stored procedure in the master database first.
2. Look for the stored procedure based on any qualifiers provided (database name or owner).
3. Look for the stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Grouping

A procedure can be created with the same name as an existing stored procedure if it is given a different identification number, which allows the procedures to be grouped logically. Grouping procedures with the same name allows them to be deleted at the same time. Procedures used in the same application are often grouped this way. For example, the procedures used with the my_app application might be named my_proc;1, my_proc;2, and so on. Deleting my_proc deletes the entire group. After procedures have been grouped, individual procedures within the group cannot be deleted.

Temporary Stored Procedures

Private and global temporary stored procedures, analogous to temporary tables, can be created with the # and ## prefixes added to the procedure name. # denotes a private temporary stored procedure; ## denotes a global temporary stored procedure. These procedures do not exist after SQL Server is shut down.

Temporary stored procedures are useful when connecting to earlier versions of SQL Server that do not support the reuse of execution plans for Transact-SQL statements or batches. Applications connecting to SQL Server version 7.0 should use the sp_executesql system stored procedure instead of temporary stored procedures. For more information, see Execution Plan Caching and Reuse.

Only the connection that created a private temporary procedure can execute it, and the procedure is automatically deleted when the connection is closed (when the user logs out of SQL Server).

Any connection can execute a global temporary stored procedure. A global temporary stored procedure exists until the connection used by the user who created the procedure is closed and any currently executing versions of the procedure by any other connections are completed. Once the connection that was used to create the procedure is closed, no further execution of the global temporary stored procedure is allowed. Only those connections that have already started executing the stored procedure are allowed to complete.

If a stored procedure not prefixed with # or ## is created directly in the tempdb database, the stored procedure is automatically deleted when SQL Server is shut down because tempdb is re-created every time SQL Server is started. Procedures created directly in tempdb exist even after the creating connection is terminated.  As with any other object, permissions to execute the temporary stored procedure can be granted, denied, and revoked to other users.

To create a stored procedure

You can also create a stored procedure using the SQL Server Enterprise Manager Create Stored Procedure Wizard.

To create a stored procedure using the Create Stored Procedure Wizard

See Also