.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Types of stored procedures

Posted By:Shashi Ray       Posted Date: September 22, 2008    Points: 10    Category: DataBase    URL: http://www.dotnetspark.com  

The classification of stored procedures is depends on the Where it is Stored.

Based on this you can divide it in 4 sections.
System stored procedures
Local stored procedures
Temporary stored procedures
Extended stored procedures

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.

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.

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.
SELECT * from [pubs].[dbo].[shashi]
--create a global temporary stored procedure.
SELECT * from [pubs].[dbo].[shasi]
--create a temporary stored procedure that is local to tempdb.
SELECT * from [pubs].[dbo].[shashi]

 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')

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend