Stored Procedure learning 2 (creating a stored procedure)

You can use the create procedure statement to create a stored procedure. Before creating a stored procedure, consider the following:

• You cannot combine the create procedure statement and other SQL statements into a single batch.

• By default, the stored procedure creation permission belongs to the database owner, who can grant this permission to other users.
• A stored procedure is a database object and its name must comply with the identifier rules.
• You can only create stored procedures in the current database.

When creating a stored procedure, specify:

• All input parameters and output parameters returned to the call process or batch processing.

• Execute programming statements for database operations (including calling other processes.
• Return to the status value of the call process or batch processing to indicate success or failure (and cause of failure.

System stored procedures

Many management activities in Microsoft SQL Server 2000 are called System stored procedures . System stored procedures are stored inMasterCreate and store databasesSP _Prefix. You can execute system stored procedures from any database without usingMasterDatabase Name to fully limit the name of the stored procedure.

We strongly recommend that you do not createSP _Prefix. SQL Server Always searches in the following orderSP _Stored Procedures starting:

1. InMasterSearch for stored procedures in the database.

2. Find the stored procedure based on any provided qualifier (Database Name or owner.
3. If no owner is specified, useDBOSearch for the stored procedure as the owner.

Therefore, although the current database may containSP _Prefix, but always checkMasterDatabase (even if the stored procedure has been specified by the database name ).

ImportantIf the stored procedure created by a user has the same name as the system stored procedure, the stored procedure created by the user will never be executed.

 

Group

If you assign a different identification number to a process, you can create the process with the same name as an existing stored procedure. This allows logical grouping of these processes. Groups with the same name can be deleted at the same time. The process used in the same application is generally grouped in this way. For exampleMy_appThe application process may be namedMy_proc; 1,My_proc; 2. DeleteMy_procDelete the entire group. After you group a process, you cannot delete a single process in the group.

Temporary Stored Procedure

The private and global temporary stored procedures are similar to the temporary tables. You can create them by adding the # And # prefixes to the process name. # Indicates a local temporary stored procedure. # indicates a global temporary stored procedure. After SQL Server is disabled, these processes no longer exist.

Temporary stored procedures are useful when connected to earlier versions of SQL Server. These earlier versions do not support the re-use of transact-SQL statements or batch processing execution plans. Applications connected to SQL Server 2000 should useSp_executesqlSystem stored procedures without using temporary stored procedures. For more information, see cache and reuse of execution plans.

This process can be executed only when a connection in the local temporary process is created. When the connection is closed (the user logs out of SQL Server), the process is automatically deleted.

Global temporary stored procedures can be executed for any connection. The global temporary storage process does not exist until the connection used by the user who created the process is closed and the current execution version of the process used by all other connections is completed. Once the connection used to create this process is closed, the global temporary storage process cannot be started. Only connections that have started to execute the stored procedure are allowed to run the process.

IfTempdbWhen you create a stored procedure without a prefix of # Or # in the databaseTempdbThe stored procedure is automatically deleted when SQL Server is disabled. Directly inTempdbThe creation process exists even after the connection is terminated. Like any other object, you can grant, deny, and revoke the permission to execute the temporary stored procedure to other users.