It's Time To Get Over That Stored Procedure Aversion You Have

Jaime

That's fine. Both of my techniques above work with very minor tweaks with any number of databases.

Groaner

If the best way you can think of to track dependencies is to do a textual search through the code for the name, I can understand why you don't appreciate moving code to environments with better tools.

I'm open to suggestions.

@Jaime said:

This is your thread about how the rest of us are morons for not doing it in stored procedures. You need to show us a reason why, we don't need to show you.

It's my thread where I posted an article that I generally agree with to start a discussion about the merits of stored procs vs. ORMs in the hope of seeing a wide range of opinions. So far, that plan has worked for the most part.

@Jaime said:

Even the article you linked admits that business logic doesn't belong in the database.

Did you miss the part where that isn't feasible when security rules apply to business processes, not data modifications unless the stored procedures are the business layer?

Come to think of it, I don't think I've stated my exact position with respect to the business layer in this thread. I may have hinted at it above when I expressed my disapproval for sp_send_dbmail, but to be more explicit, I feel that business logic THAT PERTAINS TO THE DATABASE should be in procs/views/functions. Not all business logic. Sending an email should be in the app. Rendering an HTML table should be in the app.

@Jaime said:

You didn't add a layer of defense, you simply moved all security to the database.

In the scenario I presented, the app can still have its own security on top of the database. Having a set of whitelisted stored procedures/views on the app's user account makes it slightly more difficult for little Bobby Tables, since he wouldn't be able to select/insert/update/delete/drop any of the base tables.

Now, you are correct that SQLi can exist in procs, and a proc with dynamic SQL that's signed with a certificate wouldn't necessarily be safe.

@Jaime said:

I can go all day. Fortunately for me, I have a ton of experience with SQL Server (as in, it was the primary thing I did from 1998 until 2005) and I am fairly competent with Oracle. I don't choose not to default to stored procedures out of ignorance, I actually know the facts on both sides very well.

And around 2005-2008 is when SQL Server started to get good. If I had to deal with 2000/7 or earlier on a regular basis, I'd probably want to shoot myself.

@Jaime said:

That isn't a problem with triggers, it's a problem with Paul.

That defense never works for me when I try to defend C++ on these forums.

@Jaime said:

Don't use INSTEAD OF triggers. There's no need to use them for the scenarios I mentioned. Pretty much the only time to use them is to make a view pretend to be a table.

Fair enough.

@Jaime said:

Then you move it to the business layer.

Well, guess we can't use a trigger for that anymore.

@Jaime said:

Yay, you have an anecdote. That only proves that it is possible to have a maintainable solution based on stored procedures, not that stored procedures generally increase maintainability.

It also proves that using stored procedures does not always result in an unmaintainable mess.

@Jaime said:

You also have a straw man.

I hear they like to hang out on these forums.

@Jaime said:

Just because some moron doesn't use stored procedures, you can't leap to the conclusion that his lack of use of stored procedures was the cause of the chaos.

Okay, then what would you do in that situation? You have several hundred reports with mostly the same embedded query but with slight variations between them, and these (mission critical) reports are preventing any major changes to the system.

Remember the join problem I mentioned above? A refactor of the schema has been proposed that's going to require joining a couple new tables to get the same data the reports expect. Would there be an easy way to identify, isolate, and replace each occurrence when you're using an ORM*? Would it be easier than looking at all the SELECTs, trying to find common ground between them, and writing a few to a few dozen procs that you can then drop in as replacements?

*I'm actually genuinely curious, if only for academic reasons and esprit d'escalier. Not that management would have approved of it or if it would have been feasible to implement given the sparse resources we had.

@Jaime said:

ORMs were invented to solve exactly the problem your idiot had.

Calling him an idiot is a quick way to get on my good side.

@Jaime said:

Also, reporting is a special case.

It can be an especially delicate one, too, if the bigwigs make big decisions based on the numbers.

@Jaime said:

It is folly to hate stored procedures so much that you would rather have chaos than procs. I'm saying that stored procedures are fine, but better stuff exists for 95% of circumstances.

Fair enough.

Groaner

@tarunik said:

Database feature suggestion: autoversioning of stored procedures, with atomic update and rollback.

You could implement that with a DDL trigger and a table if you wanted.

boomzilla

@galgorah said:

As for my earlier comment about modifying application code. I was referring to the queries themselves. Its much easier if its in a procedure. I make my tweak and alter the procedure. Ideally also checking in the procedure update to source control.

It's easier for you, sure. Then again, our DBAs are just specialized admins (though actually, they're sysadmins, too, so really just general admins). They don't really have anything to do with queries.

boomzilla

@Groaner said:

a discussion about the merits of stored procs vs. ORMs

I'm still whooshing a bit on this (possibly because my experience with ORM is limited, breadth-wise, and I guess sprocs, too). It sounds like discussing the merits of SELECT queries vs using views.

@Groaner said:

Remember the join problem I mentioned above? A refactor of the schema has been proposed that's going to require joining a couple new tables to get the same data the reports expect. Would there be an easy way to identify, isolate, and replace each occurrence when you're using an ORM*?

This would depend on the ORM, but in Hibernate, you'd just update your classes and the annotations on them and it would be taken care of for you in the SQL that is ultimately generated. This is an abstraction that really does work well (with the usual caveats about scaling, of course).

RaceProUK

@boomzilla said:

@Groaner said:
a discussion about the merits of stored procs vs. ORMs

I'm still whooshing a bit on this (possibly because my experience with ORM is limited, breadth-wise, and I guess sprocs, too). It sounds like discussing the merits of SELECT queries vs using views.

More or less; the two are reasonably equivalent comparisons. As to which is better… I dunno. Depends on your requirements I guess.

galgorah

@boomzilla said:

It's easier for you, sure. Then again, our DBAs are just specialized admins (though actually, they're sysadmins, too, so really just general admins). They don't really have anything to do with queries.

Our DB team is a mix. Some folks specialize in the development support side, others focus on the operational side. Everyone on the team does do a bit of the other side. About 30%. Often times DBAs are also expected to be embedded within development teams. You sit with them and attend their meetings, etc. There's a large focus on team work here.

Shoreline

From what I've seen (probably just pear, I honestly don't remember... it came with zend), ORMs are great for boring run-of-the-mill record fetches. You want all the records with this id, it's fine. As long as you can write a custom joins, even if it's just for development/experimental value. If that's all you do, the ORM becomes less useful.

That said, I've been doing a lot of mongo lately...

Jaime

@Shoreline said:

ORMs are great for boring run-of-the-mill record fetches. You want all the records with this id, it's fine.

Get records with this id and get records with these ids are the most popular lookups done by an application.

Over and above that, for me, the main customization I do to my ORM is to hand write WHERE clauses for my get operations. Some people like how Entity Framework and LLBLGen allow you to write expressions in the host language that are translated to WHERE clauses. I'm sure a lot of other ORMs work the same way. Linq allows .Net developers to craft set-based expressions in C# or VB and these expressions are translated to platform specific set-based SQL - including joins. I don't make use of this very much because it exposes data layer details all the way through the layers of the application. However, some people like it. Unlike stored procedures - if you change your schema, you get little squigglies in Visual Studio that tell you where the new schema is incompatible with the current code.

tldr; If your ORM doesn't suck, you can get the data you need without sacrificing efficiency.

tar

https://www.youtube.com/watch?v=DUAK7t3Lf8s&t=9

tar

@Jaime said:

```
CREATE TRIGGER tr_Paul_Is_An_Idiot
ON ConfigSettings
FOR INSERT, UPDATE, DELETE
AS
ROLLBACK TRAN
Who's Paul?

Jaime

@tar said:

Who's Paul?

Paul is the guy who decided that a certain config table shouldn't be updated, ever. He implemented this decision by creating a trigger that silently rolls back all changes made to the table hoping that everyone would get so confused that they'd stop trying. Of course all he did was cause us to disable the trigger every time we updated the table.

glathull

Fucking grow a pair. Learn how to think about data.

I mostly use SQL Server. I kind of love it. But when I get a new opportunity, I will learn MySQL, even though I kind of hate it, And I'll learn PostGreSQL.

And I'll learn SQLAlchemy.

You are a bunch of spoiled brats.

Onyx

I tried to check for sequitur in this post but got bitten on the ass by forgetting to do a IS NULL check first.

tar

I bet Paul wouldn't have done an IS NULL check either...

Groaner

create trigger tr_prevent_Paul_from_creating_triggers
on database
for create_trigger, alter_trigger
as
begin
    set nocount on

    declare @user sysname = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','sysname')
    declare @errormessage varchar(max)

    if(@user = 'Paul')
    begin        
        raiserror('%s is too dumb to be allowed to create triggers.', 16, 1, @user)
    end
end
go

Jaime

I'd rather give him a taste of his own medicine:

create trigger tr_prevent_Paul_from_creating_triggers
on database
for create_trigger, alter_trigger
as
begin
    set nocount on

    declare @user sysname = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','sysname')
    declare @errormessage varchar(max)

    if(@user = 'Paul')
    begin        
        rollback tran
    end
end
go

dkf

Can you prevent him from seeing that trigger too? That would be evil.