Stored Procedure is a Set precompiled Sql statements that used to Perform A special Task.
Stored Procedures are a batch of SQL statements that can be executed in a couple of ways.
A stored procedure, which calls itself, is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL does not support it well. Check your version of MySQL before using recursive stored procedure
• SP have repeatedly using data. It helps to reuse the code.
• SP is reduces the complexity of code in code behind.
• SP increase the security to application, it protect from Sql injection and hacking.
• Code maintenance and changes are done very easily. Instead of changing the code in code behind if changes required.
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.
Sample of creating Stored Procedure
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
SELECT FirstName, LastName FROM Person.Person;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO
a) Stored procedure allows modular programming.
You can create the procedure once, store it in the database, and call it any number of times in your program.
b) Stored Procedure allows faster execution.
If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
c) Stored Procedure can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
d) Stored procedures provide better security to your data
Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.
In SQL we are having different types of stored procedures are there
Stored Procedures are a batch of SQL statements that can be executed in a couple of ways.
A stored procedure, which calls itself, is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL does not support it well. Check your version of MySQL before using recursive stored procedure
• SP have repeatedly using data. It helps to reuse the code.
• SP is reduces the complexity of code in code behind.
• SP increase the security to application, it protect from Sql injection and hacking.
• Code maintenance and changes are done very easily. Instead of changing the code in code behind if changes required.
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.
Sample of creating Stored Procedure
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
SELECT FirstName, LastName FROM Person.Person;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO
Advantages of using stored procedures
a) Stored procedure allows modular programming.
You can create the procedure once, store it in the database, and call it any number of times in your program.
b) Stored Procedure allows faster execution.
If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
c) Stored Procedure can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
d) Stored procedures provide better security to your data
Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.
In SQL we are having different types of stored procedures are there
a) System Stored Procedures
b) User Defined Stored procedures
c) Extended Stored Procedures
System Stored Procedures:
System stored procedures are stored in the master database and these are starts 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
Ex: sp_helptext [StoredProcedure_Name]
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
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.
b) User Defined Stored procedures
c) Extended Stored Procedures
System Stored Procedures:
System stored procedures are stored in the master database and these are starts 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
Ex: sp_helptext [StoredProcedure_Name]
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
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.
No comments:
Post a Comment