Stored
procedure is a set of precompiled SQL statements that are used to perform a
special task. It is a group of sql statements that has been created and stored
in the database. Stored procedure will accept input parameters so that a single
procedure can be used over the network by several clients using different input
data. Stored procedure will reduce network traffic and increase the
performance. If we modify stored procedure all the clients will get the updated
stored procedure.
Types of stored
procedures:
1.
System
stored procedures:
System stored
procedures are stored in the master database and these start with a sp_ prefix.
These procedures can be used to perform variety of tasks to support SQL Server
functions for external application calls in the system tables.
2.
User-defined
stored procedures:
User-defined
stored procedures are usually stored in a user database and are typically
designed to complete the tasks in the user database. While coding these
procedures don’t use sp_ prefix because if we use the sp_ prefix first it will
check master database then it comes to user defined database
3.
Extended
stored Procedures:
Extended stored
procedures are the procedures that call functions from DLL files. Now a day’s
extended stored procedures are depreciated for that reason it would be better
to avoid using of Extended stored procedures.
4.
Recursive
stored Procedure:
A stored procedure
which calls by itself until it reaches some boundary condition. This recursive
function or procedure helps programmers to use the same set of code any number
of times.
Advantages and
Disadvantages of Stored Procedure:
Stored
procedure can be used as a modular programming – means create once, store and
call for several times whenever required. This supports faster execution
instead of executing multiple queries. This reduces network traffic and
provides better security to the data.
Disadvantage
is that it can be executed only in the Database and utilizes more memory in the
database server.
No comments:
Post a Comment