Why Use Stored Procedure And It's Advantages

Stored procedures give us many advantages over using SQL scripts and SQL statements that are dynamically created by our windows or web applications.

Compilation and storing of the query execution plan: When SQL Server runs a query it calculates the most efficient method of processing the query and stores it in something called the execution plan. Depending on the query, a good deal of the time taken to execute a query is actually spent on calculating the execution plan.

When inline SQL statements are executed the execution plan must be generated each time the query runs. When a stored procedure is called its execution plan is stored in memory and the next time the stored procedure is called the execution plan is retrieved from memory without needing to be recalculated. This increases the speed of execution and improves database performance.

Enabling of conditional and procedural logic: When sending inline SQL statements from our applications we cannot include any conditional logic.

Stored Procedure enables us to tie as many SQL statements together with conditional logins such as IF-ELSE, SELECT CASE, and looping capabilities such as WHILE loops and CURSORs. This can lead to some very powerful querying techniques as well as dramatically reduce the number of times the application has to open and close a database connection or use the connection objects Execute command. This again helps improve database, network, and therefore application performance.

Centralized repository for DML and DDL code enabling code reuse

It isolates the code that is needed to reflect changes to the database structure thus making maintenance quicker.
The procedure can be reused from different web pages/forms/classes. The procedure can be reused by more than one application.
Code reuse means quicker development time
Code reuse means quicker debugging and maintenance time
Stored Procedure can be used to log errors in a standardized way by using the RAISEERROR

Function to write to the error log or by storing the error information in an ‘Errors’ table or emailing it to the DBA etc. This makes debugging your application easier by separating out your data access errors from your windows/web programming language errors (VB.NET / C# errors etc).

Protection from SQL Injection attacks: Inline SQL statements are at high risk from SQL injection attacks which can enable a hacker to compromise and wipe out your database. Because Stored Procedure use parameters to receive user inputted values it prevents SQL injection from being used. There have been no known cases of SQL injection where static stored procedures are used.

Stored procedure parameters also define their data type and length e.g. a FirstName parameter might be of type varchar(20) so preventing any unsuitable variables of invalid data types or lengthier strings from being substituted into the query. It also has the benefit that application users can only execute the stored procedure that implements the application’s required business rules. It prevents non-DBA’s / SQL specialists from writing poor ad hoc / inline SQL queries that don’t conform to business rules. Any queries that do not follow the application’s specified business rules can lead to invalid ‘dirty’ data in the database.

Readability: Static stored procedure code is a lot more readable than ad hoc dynamic SQL queries that are concatenated together in an application. The Stored Procedure editor window and Query Analyzer both include color formatting of SQL keywords as well as enabling the easy separation of different parts of a statement onto separate lines for easy reading.