Stored Procedure and its Advantages
MS SQL Server Stored Procedure is a set of logical groups of T-SQL statements that saves time to write the query repeatedly by saving it as a Stored Procedure. These SQL statements are grouped to perform a specific task and thereupon can be called number of times. Furthermore, it also allows you to pass the parameters to get the required output.
Benefits of Stored Procedure
There are also various advantages of using the Stored Procedure
Increase in Performance: Stored Procedures are compiled into a single execution plan and will be cached in memory. So, when the same Stored Procedure is executed again the plan is cached for reuse. This in turn escalates the performance.
Reduce Network Traffic: When compared with SQL queries, Stored Procedure reduces network traffic as it is executed as a single statement. You only need to execute the name of the procedure to the network thus significantly reducing the traffic between the server and the client.
Code Reusability: Stored Procedure eliminates the need to write the code repeatedly and can also be used with multiple applications.
Maintainability: You can change the logic in the procedure without having to edit at all other places or application which makes it easier to maintain.
Stronger Security: The user can be granted access and modify data only through Stored Procedure, thus controlling what data a user has access to and protects the vital database objects.