The Developer Arguments for Stored Procedures

Question

The Developer Arguments for Stored Procedures

Steve Jones - SSC Editor

SSC Guru

Points: 736445
More actions
March 6, 2020 at 12:00 am

#3714186

Comments posted to this topic are about the item The Developer Arguments for Stored Procedures

Viewing 15 posts - 1 through 15 (of 71 total)

You must be logged in to reply to this topic. Login to reply

Kevin Steffer [MCP] Grasshopper Points: 11 More actions · Answer 1

When working with SP inside a .net project feels like Blackbox development. The Code you write looks like it assumes too much and needs a magic trust to what data an SP will return.

With Linq2sql you get compiler warnings when you cast types wrong or misspell fields etc. That is much faster to develop with Linq2Sql and less error prone.

mschluper Grasshopper Points: 19 More actions · Answer 2

In my understanding, around 2005 people started to notice that developers were putting business logic in stored procedures, and they advised against this practice. When this advice was picked up, it was verbalized as discouraging the use of stored procedures altogether.

When people speak in absolutes, they are often wrong. I still like using stored procedures to hide SQL implementation details and keep my nodejs code more readable.

Xedni SSCertifiable Points: 6951 More actions · Answer 3

I had a similar difficulty in explaining why I felt procs were preferable to EF. My initial reaction was very strongly in favor of procs over EF, although after calming down a little, I did actually spend quite a bit of time learning about EF and trying to bridge my knowledge gap between the two worlds.

The strongest argument I made was that procs and views are to sql as interfaces are for OO code. You should never be exposing class internals to another class because it tightly couples the to services, often in ways you never anticipated. That abstraction layer is critical in OO code because it frees you to make whatever changes you need to, as long as you can still satisfy the interface. Sql desparately needs such a construct, because changing the db is much harder than changing app code (in general).

As for the proc based approach, there's definetly some merit to saying procs can get overly cumbersome. Many orgs don't have a fleshed out data access layer *other* than using an ORM. In such cases, there are likely other growing pains associated to going deeper into sql programmability.

I will also sometimes point out that a a proc base solution I develop means if it breaks, ill be the one fielding the call at 11am, not them.

The other thing I find helpful (assuming you have receptive app devs) is to take the time to explain the problema I think will arise from an approach, and get them to convince me its the better choice. Often we both learn something. So convincing arguments are great, but pound for pound you can't beat a good rapport.

Executive Junior Cowboy Developer, Esq.[/url]

David.Poole SSC Guru Points: 76034 More actions · Answer 4

I was a developer long before I became a DBA. I went on the two original SQL 6.5 courses. I realised that the ability to write stored procedures made my life as a developer easier.

IMPORTANT POINT ONE: Developers have to be able to write stored procedures

I mean they need sufficient access to write the stored procs for themselves, they need to make mistakes for themselves they need help to learn, they need mentoring such as they would get in their normal role from a senior developer.

They won't be fans of stored procs if it is something that only the priesthood of DBAs, data team etc get to write.

IMPORTANT POINT TWO: Neither developers or Data people can afford to be a one trick pony, no matter how good that trick might be.

My stuff has to work well with other peoples stuff and vice verse. Producing the ultimate database is not the goal, producing the ultimate code is not the goal. Producing the overall system that fulfils the business need and is at worst cost neutral is the goal.

Learning a bit about each others world will help each be more sympathetic to the other.

IMPORTANT POINT THREE: Culture comes from the top and culture trumps strategy

Behaviour descends to the lowest level that management will tolerate. If the technical leaders have strong biases then those biases and behaviours will permeate downwards. If the boss believes that one discipline is lesser than another then if they are not careful to hide that bias it will spread through their reports downwards and will last long after they have left.

If the boss comes from a "Stored Procs are bad" background then you have a problem.

The legacy of poor or absent leadership lasts far longer than that leadership.

IMPORTANT POINT FOUR: Business logic is too vague a term to be useful

What happens if a business function is best expressed as a set based operation? Do you insist in doing it in some non-database tier of the code?

What happens if a business function is best expressed as complex iterative logic? Do you insist in embedding it in a stored procedure?

Can we not have some common sense rather than perpetuating the drivel about business logic? The genius of AND vs the tyranny of OR! Again, if developers are allowed to, are comfortable with and encouraged to write stored procs then it becomes a case of saying this function is best off expressed as set based logic, this function is better off done in whatever coding language is appropriate.

IMPORTANT POINT FIVE: Understand the difference between storage, presentation and interaction

Just because developers want to pass around data as JSON object doesn't mean that it has to be stored as JSON objects. Just because data might be stored in a relational form does not mean that it has to be presented in tabular format.

IMPORTANT POINT SIX: Sometimes stored procs are NOT the answer!

In a data warehouse/mart they are useful for data pipelines but for end users of the data mart probably not so much.

IMPORTANT POINT SEVEN: Sometimes stored procs ARE the answer

I think of them as either public methods for objects hiding private members or as encapsulations. This can be incredibly important for OLTP systems. They can abstract the complexity away from the app. Again, the procs have to be visible and writable by the developers.

LinkedIn Profile

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

I had this same conversation with an Nhibernate developer

he said it was just faster than writing stored procs

I asked him "would you write a c# class that exposed all of it's internal peoperties... you write let/get methods for each class"

reply "yes"

my reply "stored procedures are the equivalent of methods, they protect us"

he still won't wear that argument

MVDBA

call.copse SSCoach Points: 18064 More actions · Answer 6

I'm not biting.....

damnit OK. They're necessary sometimes but not always. I think that's probably enough said TBH.

Steve Collins SSCrazy Points: 2169 More actions · Answer 7

"...from a developer point of view..." Why is it "more difficult" to build with stored procedures:

It makes your entire project dependent on the DBA. Not as satisfying as being the hero dev who does it all.
If you work with a bad DBA your projects are sunk.
It means you'll probably be given deprivileged logins (as is it should be) which means you're hierarchically underneath whoever issues the credentials (probably the DBA)
Using SP's imposes workflow and technical choices on the developer. In order for a C# dev to use a procedure they have to either use ORM (to partially automate) or manually translate and re-code each input/output variable, create a method (1) to call Sql, create a method (2) to evaluate the result from sql and then translate the output into a format usable by the application. It's more work.
The manual option for running code ADO.NET is a mishmash of alternate methods some of which are not type safe. The whole database/C# connection is not thread safe. So there's specific knowledge about exception handling it's would be good to know. Resilience and error logging are DIY. For Sql to C# error logging there are no standard interfaces afaik.
The appropriate C# SQL type libraries are relatively new and have little functionality compared to the base CLR types. None of the ORM's including EF use the type libraries appropriately afaik.
From a C# perspective it's not unit testable because there are persistence constraints. "Non testable" means less manageable and less confidence to migrate. Doesn't easily integrate with "modern" CI/CD pipelines.
It can be very difficult to debug. It sometimes tricky to know whether a bug is caused by SQL, ADO.NET, or C# or some combination. "Linting" or console sink logging is DIY.
To be really effective you have to know Sql well

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

austin.mcguire SSC Journeyman Points: 78 More actions · Answer 8

In today's cloud world vendor lock-in is an issue with most stored procedures being DB platform specific (MSSQL vs PLSQL vs MySQL). Having to deploy an additional server just to support your product's platform is a real expense that can drive customers to your competitor. Using DB abstraction tools like EF make your code more DB platform agnostic, but they typically do not directly support SPs. Like David Poole's Points 6 and 7, you have to know the requirements and if DB Platform Diversity is a requirement, that pretty much rules out SPs.

dmbaker SSCertifiable Points: 5139 More actions · Answer 9

Xedni wrote:

I had a similar difficulty in explaining why I felt procs were preferable to EF.

By "EF" do you mean "Entity Framework" as in Microsoft? You do realize that you can use stored procedures with Entity Framework (though MS made it more difficult with EF Core)? Use of an ORM does not (generally) preclude the use of stored procedures, and using stored procedures (and views) with Entity Framework works well to isolate your code from direct table access.

I'm not necessarily advocating for or against stored procs (I prefer them, personally, and I use Entity Framework), it just bugs me when I hear people thinking that using an ORM precludes the use of stored procedures. 🙂

Steve Collins SSCrazy Points: 2169 More actions · Answer 10

dmbaker wrote:

Xedni wrote:
I had a similar difficulty in explaining why I felt procs were preferable to EF.
By "EF" do you mean "Entity Framework" as in Microsoft? You do realize that you can use stored procedures with Entity Framework (though MS made it more difficult with EF Core)? Use of an ORM does not (generally) preclude the use of stored procedures, and using stored procedures (and views) with Entity Framework works well to isolate your code from direct table access.

Just curious, what database privileges belong to the login EF is using to execute those stored procedures? Is it db_reader and db_writer?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Alex Gay SSCrazy Points: 2219 More actions · Answer 11

I seem to be in the minority here as when I was a jobbing developer I insisted that all of the necessary SQL code was encapsulated in Stored Procs. Admittedly we were a small team of 6-8 people and we each tended to work on our own projects and were responsible for both the application and database code. I just got fed up with having a minor change to the database force me to edit, recompile, and deploy the application again. By forcing all of the SQL into Stored Procs I could just edit the procedure instead, and as long as the returned dataset was the same everything kept working. This reduced downtime and made fixes fast.

They also allow you to push out a version 2 with extra functionality while still supporting version 1, as version 2 can use new stored procedures where necessary. If you want to look like the Great Big Hero(tm) stored procs make you look good. I didn't want to be a hero, I'm just lazy and would rather trade a little more effort now for an easier life in the future.

And LINQ->SQL is devil spawn.

dmbaker SSCertifiable Points: 5139 More actions · Answer 12

(sorry, supposed to be a quote/reply)

This reply was modified 4 years, 9 months ago by dmbaker.

dmbaker SSCertifiable Points: 5139 More actions · Answer 13

Steve Collins wrote:

dmbaker wrote:
Xedni wrote:
I had a similar difficulty in explaining why I felt procs were preferable to EF.
By "EF" do you mean "Entity Framework" as in Microsoft? You do realize that you can use stored procedures with Entity Framework (though MS made it more difficult with EF Core)? Use of an ORM does not (generally) preclude the use of stored procedures, and using stored procedures (and views) with Entity Framework works well to isolate your code from direct table access.
Just curious, what database privileges belong to the login EF is using to execute those stored procedures? Is it db_reader and db_writer?

I don't think I'd ever use a fixed database role for this purpose. I typically define a role specifically for use by the application, assign that role the select/execute privs on my views/procs, then add users to that database role.

So no, it's not db_reader/db_writer, it's a specific role I create, specifically for use by my application. So users have my database role and nothing else (well, I guess they have "public" but we don't give that access to anything).

Jason- SSCrazy Points: 2795 More actions · Answer 14

My standard design is that the R part of CRUD is done via views and the CUD part of CRUD is done through stored procedures. (in case CRUD is unfamiliar - Create Read Update Delete). This adheres to abstraction rules and principles quite well. There are many benefits to it, but three that keep me coming back to this as the goto design.

Supportability - T-SQL is something that the DBA, Application support team and even most end users can read pretty well and having the code of the stored procedure available makes troubleshooting and data flow understandings a bit more stream lined and keeps a lot of issues from interrupting the developers. It's more difficult to track down issues for non-developers when logic of writing data is veiled in compiled or unfamiliar code.
DRY (Don't Repeat Yourself) - Not all processes can (or easily do) call dlls or APIs etc. With this design writing to the database means calling the same object (procedure) weather you're the application writing 1 or 1m rows or an SSIS package writing 1 or 1m rows, or powershell script or so on and so on. (Note: we build procs to accept multiple rows using TVP, JSON or XML and the onus of applying business rules/logic is on the caller of the procedure and onus of "business data rules/logic" is on the procedure(s), this means as far as data rules are concerned developers don't have to maintain that logic in the app and then in ETL and then again in reports etc.)
Security - This design simplifies security. In almost every case weather an app requires users to have permissions to a database (though this is generally frowned upon) or in SOA the permissions are the same, all tables go into a separate schema for data in which no permissions are granted and various other schemas contain views and procs with SELECT and EXECUTE permissions applied as appropriate. This enables us to ensure all CUD is done by the app or ETL etc (except for those pesky sysadmin and db_owner members no data manipulation can occur outside of a procedure and that means no bypassing data governance rules, auditing, security checks etc).

It's a pretty solid design and works for most solutions (I actually haven't found where it doesn't but I leave the possibility open). Still, I tend not to have any hard absolute rules except for around security and there are times and environments where this won't fly. Usually stemming from political arguments, time constraints (perceived or real) and change resistance (That's not how we're used to doing it.) so I try to remain flexible, unwilling to harm the working relationships if for some reason this design just isn't accepted. There are other (usually more involved) ways to provide the above three (especially 2 and 3 and 1 is subjective).

-