Stored Procedure :
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.
Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using
different input data. And when the procedure is modified, all clients automatically get the new version.
Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc
Stored procedure by definition is a segment of code which contains declarative or procedural SQL statements.
A stored procedure is resided in the catalog of the database server so we can call it from a trigger, another stored procedure or even from client appliations.
As the definition above, the stored procedure can contains any SQL statement like INSERT, UPDATE and DELETE or any SQL data definition like CREATE TABLE, ALTER TABLE and etc. In addition, a stored procedure also supports procedure statements such as IF ELSE, WHILE... to make it as powerful as another programming languages such as C/C++, C# and Java.
Using stored procedure has several advantages :
1).It is used to increases the performance of application because when we create stored procedures, they are compiled and stored in database catalog. Later when client applications call them, they are generally executed faster than uncompiled SQL statements which are sent from the client applications.
2) The network traffic between application server and database server is also signification reduced because the applications don't have to send such long and uncompiled SQL statements to the server to get the data back.
3)Stored procedures can be used for database security purpose because each store procedure can have its own database privileges.
4)One of the most advantage of stored procedure is code reusability. Once created, a stored procedure can be reused over and over again by multiple applications.
It is the best to illustrate the ability of stored procedure by showing examples. You can follow this tutorial to understand more about stored procedure. We will use Microsoft SQL Server to demonstrate stored procedure, you can also use MySQL with a change a little bit because of specification of each database server is different. Start learning how to write a stored procedure by following the tutorial getting started with stored procedure
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.
Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using
different input data. And when the procedure is modified, all clients automatically get the new version.
Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc
Stored procedure by definition is a segment of code which contains declarative or procedural SQL statements.
A stored procedure is resided in the catalog of the database server so we can call it from a trigger, another stored procedure or even from client appliations.
As the definition above, the stored procedure can contains any SQL statement like INSERT, UPDATE and DELETE or any SQL data definition like CREATE TABLE, ALTER TABLE and etc. In addition, a stored procedure also supports procedure statements such as IF ELSE, WHILE... to make it as powerful as another programming languages such as C/C++, C# and Java.
Using stored procedure has several advantages :
1).It is used to increases the performance of application because when we create stored procedures, they are compiled and stored in database catalog. Later when client applications call them, they are generally executed faster than uncompiled SQL statements which are sent from the client applications.
2) The network traffic between application server and database server is also signification reduced because the applications don't have to send such long and uncompiled SQL statements to the server to get the data back.
3)Stored procedures can be used for database security purpose because each store procedure can have its own database privileges.
4)One of the most advantage of stored procedure is code reusability. Once created, a stored procedure can be reused over and over again by multiple applications.
It is the best to illustrate the ability of stored procedure by showing examples. You can follow this tutorial to understand more about stored procedure. We will use Microsoft SQL Server to demonstrate stored procedure, you can also use MySQL with a change a little bit because of specification of each database server is different. Start learning how to write a stored procedure by following the tutorial getting started with stored procedure
0 comments:
Post a Comment