Should i use stored procedures

Yet they provide value for a cost, right? Not all businesses can afford to do everything that claims to provide value for a cost because not all businesses can afford every cost. Maybe attend a local user group and talk to other folks who are developing apps, or talk to your project managers about how they decide to prioritize some things over others.

Used to use stored procedures for every operation we did in our system. It just became a mess in the end to source control, test, and deploy. Mark Messer, Yes, you need to consider all the different considerations of your organization.

Normally you should take a good hard look at what data store you are going to use since it is more rare and sometimes even foolish to switch databases.

A Bit like a class. Makes it a lot simpler to keep everything together rather than a lot off little stored procs. Having all access to the database performed via a stored procedure isolation layer, allows database changes to be made without having to refactor application code.

As long as the stored proc layer provides the same result set and the call parameters remain the same, the database structure can be changed without these changes affecting the application. The second and perhaps the greatest advantage is architectural — this isolation layer enables data governance, where the business users are responsible for producing the data dictionary that underpins the database structures and for governing the quality of the data in the database.

If they decide that the definition needs to change e. Implement a stored proc isolation layer so that the database structures can be changed and only a few stored procs need to be rewritten, rather than have pervasive associated application changes having to be made, and the problem disappears. Mark — slow up a little and read the post, chief. In this case, you are not talking to a junior database administrator but to someone who has 40 years of data architecture experience and who has delivered the Charles Schwab enterprise data warehouse, as well as recovered the project to integrate the BI platforms for Lloyds Bank and HBOS — two of the largest banks in the world.

Look up from what is good for a single application and consider the larger IT picture. Scattering database access code across multiple applications is a really bad idea when it comes to the big picture. It essentially disables any data governance initiative due to the effort involved in making the changes needed to have a common data dictionary, which in turn makes integrating systems virtually impossible and building enterprise data warehouses and MDM systems extremely difficult.

I have heard this promise many times over the years, but unfortunately I have yet to even hear an anecdotal story of it paying off. Reason being is that the change is so big you have to refactor the app anyway, or small enough to do with a view. Or … these kinds of refactors just are never needed. All are basically free. And our experience confirms. In our shop, it paid off for about of the s of data-access only therefore written just for that purpose SPs we wrote over the past 15 years.

Al, yes, I have seen this done successfully, in multiple cases. What it does require, however, is active management of the library of stored procedures that are developed and a planned approach by the data architect as to which SPs get built. The approach no only enables data governance, with its multiple associated benefits, but also has a number of benefits with regards to database efficiency, code maintainability, enables the database engine deadlock detection and resolution mechanism, has security and encryption benefits, etc.

The same messed up query will poor perform inside or outside an SP. If you need fine tuning or have high performance requirements, hire an SQL Developer to write efficient queries, in code or in SPs preferably in SPs because of all the reasons you mentioned.

Your email address will not be published. Don't subscribe All Replies to my comments Notify me of followup comments via e-mail. You can also subscribe without commenting. Post Comment. Want to advertise here and reach my savvy readers? Should we use stored procedures or queries built in the app? Last Updated March 27, Brent Ozar. Leave new Budd. Jason Markantes. Greg Low. Jeff Mergler. Brent Ozar. Jeff Moden. Obviously, I have the opposite scars of Brent.

John Zabroski. That still happens today ? I would love to read what they were doing. Bryan Rebok. Alex Friedman. Gary Green. If you dynamically generate the query text, then each distinct formulation will be cached. Bryant McClellan. Randy Pitkin. Al Budelier. Whenever possible. So, as much as I love SQL, it has its warts. John Hennesey. Phil Jacobs. Return a status value to a calling procedure or batch to indicate success or failure and the reason for failure.

You make the decision of interacting with the backend database via stored procedures or by including the SQL in your application code. Let's take a closer look at each approach.

The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:. They allow modular programming. They allow faster execution.

They can reduce network traffic. They can be used as a security mechanism. You can create a stored procedure once, store it in the database, and call it any number of times in your program. Someone who specializes in database programming may create stored procedures; this allows the application developer to concentrate on the code instead of SQL. During creation, stored procedures are parsed and optimized, and you can use an in-memory version of the procedure after the procedure executes the first time.

You can perform an operation that requires hundreds of lines of T-SQL code through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network. And, you can grant users permission to execute a stored procedure even if they don't have permission to execute the procedure's statements directly. While stored procedures offer a number of benefits, placing SQL directly in your application code isn't necessarily wrong.

One excellent example is SQL that is generated on the fly. Its dynamic nature negates many of the advantages of a stored procedure. That is, you cannot create and cache an execution plan since it's different each time it's called. The difference with this scenario is where the code is executed i. Another situation in which you might have to decide between constructing dynamic T-SQL in your application or on the database server is the passing of data to the stored procedure.

Ultimately, it depends on what developers are most comfortable with. If a developer has lots of app development experience, they might prefer queries in code. Personally, I think having queries in code can enable developers to move much faster and security concerns can be mitigated by ensuring teams are following best practices e. Stored procs aren't a "silver bullet" for system security.

Maybe in low level and rare scenarios or if we write code for a legacy companies with unfounded restrictions, stored procedure should be an option. On top of the speed and security considerations, I tend to stick as much in Stored Procedures as possible for ease of maintenance and alterations. If you put the logic in your application, and find later that sql logic has an error or needs to work differently in some manner, you have to recompile and redeploy the whole app in many cases especially if it's a client side app such as WPF, Win-Forms, etc.

If you keep the logic in the stored proc, all you have to do is update the proc and you never have to touch the application. The use case I think is extremely compelling and extremely useful is if you are taking in a lot of raw information that should be separated out into several tables, where the some of the data may have records that already exist and need to be connected by foreign key id, then you can just IF EXISTS checks and insert if it doesn't or return key if it does, which makes everything more uniform, succinct, and maintainable in the long run.

If you have a git repository full of everything anyone would need and is easily understandable, that can be very valuable. The stored procedures are a method of collecting operations that should be done together on database side , while still keeping them on database side. You, therefore, should make stored procedures as easy to maintain as all your other code. I'm not opposed to stored procedures in their place, but gratuitous use of stored procedures can be very expensive.

First, stored procedures run on the database server. That means that if you have a multi-server environment with 50 webservers and one database server, instead of spreading workloads over 50 cheap machines, you load up one expensive one since the database server is commonly built as a heavyweight server.

And you're risking creating a single-point-of-failure. Secondly, it's not very easy to write an application solely in stored procedures, although I ran into one that made a superhuman effort to try to. So you end up with something that's expensive to maintain: It's implemented in 2 different programming languages, and the source code is often not all in one place either, since stored procedures are definitively stored in the DBMS and not in a source archive.

So aside from a fairly messy app architecture, you also limit the set of qualified chimpanzees who can maintain it, as multiple skills are required. You need to maintain some sort of data integrity across multiple systems. That is, the stored logic doesn't belong to any single app, but you need consistent behavior from all participating apps. A certain amount of this is almost inevitable in modern-day apps in the form of foreign keys and triggers, but occasionally, major editing and validation may be warranted as well.

You need performance that can only be achieved by running logic on the database server itself and not as a client. So it behooves you to ensure that if there are significant bits of the offending operation that CAN be offloaded onto clients, you can separate them out and leave the most critical stuff for the DBMS server.

Another scenario would involve use cases where it does some protocol when handling the tables hint: defined steps which transactions are likely to be involved , this could benefit from locality of reference: Being in the server, queries might benefit.

OTOH, you could supply a batch of statements directly into the server. Specially when you're on a XA environment and you have to access federated databases. If you are talking business logic rather than just "Should I use sprocs in general" I would say you should put business logic in sprocs when you are carrying out large set based operations or any other time executing the logic would require a large number of calls to the db from the app.

It also depends on your audience. Is ease of installation and portability across DBMSs important to you? If your program should be easy to install and easy to run on different database systems then you should stay away from stored procedures and also look out for non-portable SQL in your code. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. When should I use stored procedures? Ask Question. Asked 12 years, 3 months ago.

Active 4 months ago. Viewed 26k times. Are there any rules of thumb that you can think of in reference to this? Improve this question. IAdapter Suggest removing the php tag as php could easily be any other programming language. This very extensive answer may better answer your question then the answers that are provided here. Add a comment. Active Oldest Votes. Improve this answer. Just wanted to restate that. The SQL goes in a stored procedure, the procedure is called from code.

No code ever touches even so much as a select statement. Running an SP or not is the difference between pre-compiled and interpreted code- which one do you prefer? The "well-documented" performance item is actually a non-issue depending on the database engine you are using. When using procs you can deny direct table access and thereby completely secure yourself against most forms of destructive sql injection.

Otherwise you are dependent on the code to stop it; which not all programmers are created equal. Chris Lively: Parameterized queries are the most secure against sql injections. See palisade. KB: first, sql injection is just one attack vector against databases. Second, parameterized queries will not save you from someone uploading a malicious document with embedded code.

Nor will it save you if a developer forgets to do it. However, simply not allowing direct table access in the first place will prevent all of that. And you make it impossible for code to select the data it wants.

I cannot find value on SP for everything. Show 3 more comments. With a corporate database the asset is valuable and invalid data or actions can have business-threatening consequences. Your primary concern is safeguarding the business, not how convenient access is for your coders. Such databases are by definition accessed by more than one application. You need to use the abstraction that stored procedures offer so the database can be changed when application A is upgraded and you don't have the resource to upgrade application B.

Similarly the encapsulation of business logic in SPs rather than in application code allows changes to such logic to be implemented across the business more easily and reliably than if such logic is embedded in application code. For example if a tax calculation changes it's less work, and more robust, if the calculation has to be changed in one SP than multiple applications.

The rule of thumb here is that the business rule should be implemented at the closest point to the data where it is unique - so if you have a specialist application then the logic for that app can be implemented in that app, but logic more widely applicable to the business should be implemented in SPs.

Cruachan Cruachan Bad examples do not eliminate the advantages when a method is used properly. An example is always limited and never proves a generalization.

That's ridiculous. I'm not advocating splitting business logic between the middle tier and the data layer- but your middle tier should be using SPs exclusively to deal with the data. It's a performance thing, unless you're using some crappy rdbms that doesn't precompile store procedures. I'd like to hear one single "Serious Downside".