Why we are using store procedure?

Store procedures are just a set of queries which can be used to run in one click. They are mainly used to carry out several operation under one command eg., If This Then That. This may raise another question in you, Then what is Trigger. Triggers are designed to do the task at a particular action like Insert,Update or delete, we cannot use a insert trigger for update action and vice versa. Where as a Store procedures are not designed in such a way, we can use them at any place we want and can use any kind of query we lie to use and can execute them at any point of time depending on our requirement. read less

It is depending on usage of functionality You can decide to use or not by looking at these advantages and drawbacks: Advantages Maintainability Because scripts are in one location, updates and tracking of dependencies based on schema changes becomes easier Testing Can be tested independent of the application Isolation of Business Rules Having Stored Procedures in one location means that there's no confusion of having business rules spread over potentially disparate code files in the application Speed / Optimization Stored procedures are cached on the server Execution plans for the process are easily reviewable without having to run the application Utilization of Set-based Processing The power of SQL is its ability to quickly and efficiently perform set-based processing on large amounts of data; the coding equivalent is usually iterative looping, which is generally much slower Security Limit direct access to tables via defined roles in the database Provide an "interface" to the underlying data structure so that all implementation and even the data itself is shielded. Securing just the data and the code that accesses it is easier than applying that security within the application code itself Drawbacks Limited Coding Functionality Stored procedure code is not as robust as app code, particularly in the area of looping Portability Complex Stored Procedures that utilize complex, core functionality of the RDBMS used for their creation will not always port to upgraded versions of the same database. Testing Any data errors in handling Stored Procedures are not generated until runtime Location of Business Rules Since SP's are not as easily grouped/encapsulated together in single files, this also means that business rules are spread throughout different Stored Procedures. App code architecture helps to ensure that business rules are encapsulated in single objects. There is a general opinion that business rules / logic should not be housed in the data tier Utilization of Set-based Processing Too much overhead is incurred from maintaining Stored Procedures that are not complex enough. As a result, the general consensus is that simple SELECT statements should not be bound to Stored Procedures and instead implemented as inline SQL. Cost Depending on corporate structure and separation of concern for development, there is the potential that Stored Procedure development could potentially require a dedicated database developer. Some businesses will not allow developers access to the database at all, requiring instead a separate DBA. This will automatically incur added cost. Some companies believe (and sometimes it's true, but not always) that a DBA is more of a SQL expert than an application developer, and therefore will write better Stored Procedures. In that case, an extra developer in the form of a DBA is required. read less

You can able to avoid SQL injection using parameterised concept in .Net.Main purpose of using Store procedure is that,when you want to manipulate data with multiple tables and return as single table.For eg,you want to copy the details of one person to another person,which involves five tables,its better to do such things in Store procedure. Also,using Stored procedure,you can skip the compilation of the sql statements. read less

Stored Procedures provide various advantages over inline sql statements 1. SQL Injections :- Secure as compare to Inline SQL Query. 2. Increase Database Performance :- When a Stored procedure is executed its execution plan is stored in memory and next time stored procedure is called the execution plan from memory without recalculating every time 3. Reuse :- One stored procedure is used in many places 4. Enable Security Model :- Can apply additional security setting in the Stored Procedure. read less

Stored procedures manage, control and validate data. Large queries can be avoided. Reduces network traffic since they need not be recompiled. Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code Permissions can be granted for stored procedures. Hence, increases security read less