stored procedure
Explain advantages and disadvantages of stored procedure in sqlserver?Can anyone help to answer this question?
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
paul danielPosted Mar 2, 2010, 4:30 AM
Advantage
1 easy to dynamically group your data, for instance, a subset of records
2 you can also embed/call the SP in your sql statements
Disadvantages
1 it can only be executed in the DB
2 it will utilize the DB server resources like the memory
Lalit MPosted Feb 26, 2010, 12:41 AM
And Stored Procedures Disadvantages
Stored procedures are "defined once, used many times." If any changes are necessary, the (one and only one) definition of the stored procedure must be replaced. Dynamic SQL, of course, allows any SQL query to be issued at any time.
Any change to a stored procedure instantly impacts every other piece of software, report, etc. (inside or outside of the DBMS) which directly or indirectly refers to it. It is not always possible to determine with certainty exactly what those impacts will be, nor what changes can safely be made without adversely impacting something else.
For various reasons, many organizations strictly limit who is allowed to define and issue a query against the database. Programmers and other users may therefore find themselves having no choice but to implement inefficient solutions to their problems using what stored procedures are available to them ... whether or not the procedures are appropriate for this particular ancillary task.
Furthermore, some modern DBMS systems (notably from Microsoft SQL Server 2000 onwards) don't offer any performance benefits of using stored procedures: they are compiled and cached in the same manner as dynamic SQLAmit ChoudharyPosted Feb 26, 2010, 12:17 AM
Here are some points to guide you....
Advantages:
the procedures/functions are stored in the database and are, therefore, executed on the database server which is likely to me more powerful than the clients which in turn means that stored procedures should run faster;
the code is stored in a pre-compiled form which means that it is syntactically valid and does not need to be compiled at run-time, thereby saving resources;
each user of the stored procedure/function will use exactly the same form of queries which means the queries are reused thereby reducing the parsing overhead and improving the scalability of applications;
as the procedures/functions are stored in the database there is no need to transfer the code from the clients to the database server or to transfer intermediate results from the server to the clients. This results in much less network traffic and again improves scalability;
Disadvantages
- Applications that involve extensive business logic and processing could place an excessive load on the server if the logic was implemented entirely in stored procedures. Examples of this type of processing include data transfers, data traversals, data transformations and intensive computational operations. You should move this type of processing to business process or data access logic components, which are a more scalable resource than your database server.
- Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in T-SQL. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.
- Writing and maintaining stored procedures is most often a specialized skill set that not all developers possess. This situation may introduce bottlenecks in the project development schedule.
Source ArticleDon't forget to mark the answer if it helps.