stored procedure

Question

Explain advantages and disadvantages of stored procedure in sqlserver?Can anyone help to answer this question?

Amit Choudhary · Answer

hi friend 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 Article Don't forget to mark the answer if it helps.

Lalit M · Answer

Stored Procedures Advantages 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 SQL

paul daniel · Answer

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