Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
Types of stored procedures
Posted Date: 23 Sep 2008 Resource Type: Articles Category: Databases
|
Posted By: Shashi Member Level: Gold
Rating:  Points: 7
|
The classification of stored procedures is depends on the Where it is Stored. Based on this you can divide it in 4 sections.
1.System stored procedures
2.Local stored procedures
3.Temporary stored procedures
4.Extended stored procedures
1.System Stored Procedures:
System stored procedures are stored in the Master database and are typically named with a sp_ prefix. They can be used to perform variety of tasks to support SQL Server functions that support external application calls for data in the system tables, general system procedures for database administration, and security management functions.
For example, you can view the contents of the stored procedure by calling
sp_helptext [StoredProcedure_Name].
There are hundreds of system stored procedures included with SQL Server. For a complete list of system stored procedures, refer to "System Stored Procedures" in SQL Server Books Online.
2.Local stored procedures
Local stored procedures are usually stored in a user database and are typically designed to complete tasks in the database in which they reside. While coding these procedures don’t use sp_ prefix to you stored procedure it will create a performance bottleneck. The reason is when you can any procedure that is prefixed with sp_ it will first look at in the mater database then comes to the user local database.
3.Temporary stored procedures
A temporary stored procedure is all most equivalent to a local stored procedure, but it exists only as long as SQL Server is running or until the connection that created it is not closed. The stored procedure is deleted at connection termination or at server shutdown. This is because temporary stored procedures are stored in the TempDB database. TempDB is re-created when the server is restarted.
There are three types of temporary stored procedures: local , global, and stored procedures created directly in TempDB.
A local temporary stored procedure always begins with #, and a global temporary stored procedure always begins with ##. The execution scope of a local temporary procedure is limited to the connection that created it. All users who have connections to the database, however, can see the stored procedure in Query Analyzer. There is no chance of name collision between other connections that are creating temporary stored procedures. To ensure uniqueness, SQL Server appends the name of a local temporary stored procedure with a series of underscore characters and a connection number unique to the connection. Privileges cannot be granted to other users for the local temporary stored procedure. When the connection that created the temporary stored procedure is closed, the procedure is deleted from TempDB.
Any connection to the database can execute a global temporary stored procedure. This type of procedure must have a unique name, because all connections can execute the procedure and, like all temporary stored procedures, it is created in TempDB. Permission to execute a global temporary stored procedure is automatically granted to the public role and cannot be changed. A global temporary stored procedure is almost as volatile as a local temporary stored procedure. This procedure type is removed when the connection used to create the procedure is closed and any connections currently executing the procedure have completed.
Temporary stored procedures created directly in TempDB are different than local and global temporary stored procedures in the following ways:
You can configure permissions for them.
They exist even after the connection used to create them is terminated.
They aren't removed until SQL Server is shut down.
Because this procedure type is created directly in TempDB, it is important to fully qualify the database objects referenced by Transact-SQL commands in the code. For example, you must reference the Authors table, which is owned by dbo in the Pubs database, as pubs.dbo.authors.
--create a local temporary stored procedure.
CREATE PROCEDURE #tempShashi
AS
SELECT * from [pubs].[dbo].[shashi]
--create a global temporary stored procedure.
CREATE PROCEDURE ##tempShashi
AS
SELECT * from [pubs].[dbo].[shasi]
--create a temporary stored procedure that is local to tempdb.
CREATE PROCEDURE directtemp
AS
SELECT * from [pubs].[dbo].[shashi]
4.Extended Stored Procedures
An extended stored procedure uses an external program, compiled as a 32-bit dynamic link library (DLL), to expand the capabilities of a stored procedure. A number of system stored procedures are also classified as extended stored procedures. For example, the xp_sendmail program, which sends a message and a query result set attachment to the specified e-mail recipients, is both a system stored procedure and an extended stored procedure. Most extended stored procedures use the xp_ prefix as a naming convention. However, there are some extended stored procedures that use the sp_ prefix, and there are some system stored procedures that are not extended and use the xp_ prefix. Therefore, you cannot depend on naming conventions to identify system stored procedures and extended stored procedures.
Use the OBJECTPROPERTY function to determine whether a stored procedure is extended or not. OBJECTPROPERTY returns a value of 1 for IsExtendedProc, indicating an extended stored procedure, or returns a value of 0, indicating a stored procedure that is not extended.
USE Master
SELECT OBJECTPROPERTY(object_id('xp_sendmail'), 'IsExtendedProc')
|
Responses
|
| Author: Vignesh 03 Oct 2008 | Member Level: Silver Points : 1 | Nice Post Buddy, There is 1 other type of SP (i.e.) Recursive Stored Procedure. This is a type of SP that calls itself recursively until some boundary condition is reached.
|
|