Stored Procedures

Posted Date: 21 Nov 2008	Resource Type: Articles	Category: General
Author: mahendrakiran	Member Level: Gold
Rating:	Points: 7

A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.

.They allow modular programming.
.They allow faster execution.
.They can reduce network traffic.
.They can be used as a security mechanism.

Different types of Stored Procedures are:

1.Temporary Stored Procedures:- SQL Server supports two types of temporary procedures: a)local and b)global.
a)A local temporary procedure is visible only to the connection that created it.
b)A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure.

2.System stored procedures: System Stored Procedures are created and stored in the master database and have the sp_ prefix.(or) xp_ System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.

3.Automatically Executing Stored Procedures : One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.

4.User stored procedure

Responses

Author: sangeetha 28 Apr 2009

Member Level: Gold Points : 2

Stored procedure is a set of SQL commands that have been complied and stored on the database sever. They can be used in the code as and when required since hey stored. They need not be complied over and over again. They can be invoked by CALL procedure (Procedure name) or EXECUTE procedure((Procedure name)

1.Manage, control and validate data
2.It can also be used for access mechanisms
3.Large queries can be avoided
4.Reduces network traffic since they need not be recompiled
5.Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored 6.procedures increases reusability of code
Permissions can be granted for stored procedures. Hence, increases security.

Feedbacks

Popular Tags

What are tags ?

Search Tags

(No tags found.)

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: SharePoint for Beginners
	Previous Resource: Web Security threats
	Return to Discussion Resource Index
	Post New Resource
	Category: General

Post resources and earn money!

Related Resources