Comment On Who Needs Stored Procedures, anyways?

I'm more puzzled by wtf are all those new lines being inserted into the SQL statement...

Good call, Jerry. I was too busy laughing at the size of the foolishness to even notice what they were doing there.

Not sure how apparant it is here, but the SQL statement contains lots of business logic too. Heh.

Perhaps the newlines are added so that the string van be more easily examined in the debugger<g>.

"Can be more easily examined in the debugger" that is...

Oh man.. reminds me of this huge java project (that I was not a part of thankfully) at one of my recent jobs.

After the code went into QA, the headoffice 'consultants' demanded the developers to take out ALL the (100 or so) stored procedures and hardcode the sql in the java code, transactions and all.

The reason? Their DBA's suggestion that they should "NOT" be used.

The real reason? (My guess) No procedures, no debugging/deployment/optimization issues for the DBA. If there's a bug, the developers fix it in their code and release it again. If there are performance issues, its the programmer's fault.

This one sounds like one of those cases.
( same DBA maybe? :D )

Hey! Stopping stealing my code!

"The real reason? (My guess) No procedures, no debugging/deployment/optimization issues for the DBA. If there's a bug, the developers fix it in their code and release it again"

Maybe the point is to be database independent. Using program code instead of stored procedures lets your application be portable across any other database. For example, the stored procedures are for SQL Server and you want to migrate to an open-source database (such as MySql, FireBird, PGSQL etc). We (my company's developerts) actually _do_ that, and our application runs with most of databases that support AnsiSQL.

I had a boss at a former company who when he was told inline SQL in code is bad proceeded to cut and paste all the inline SQL into procs that looked just like that...Thanks for bringing back that laugh for me.

About not using stored procedures for portability reasons...
You should at least lift out the actual sql from the code and store it in textfiles or something.
When I'm coding PHP with mysql for the data-storage I do just that since mysql doesnt support stored procedures as of yet.
Actually I wrote a whole wrapper class to handle some of the more simple things a real sp can do like taking arguments and set variables and such. Took me about a day so it's not rocket science...

Having SQL within program-code is just... wrong. :)

I have to say reading thedailywtf has been a great learning experience for me, there's stuff that I didn't know other programmers would consider horrible, list this inline SQL thing.

But I'm not convinced yet. For example suppose I need to get the name of a client based on their ID number, why is it so bad to just inline "select name from client where id = $id")? I would personally feel that opening a separate file containing this sql query and using it instead, would be both slower AND more obfuscated.

I think it's a case of readibility. If you have something that's only one line, it's usually quicker to write it inline. If it's like something above, then I'd say go with a sproc.

There's also O/R mappers that a lot of people swear by for their ease of use. Don't get into the sproc vs orm debate though as it boils down to personal preference.

Either way I usually place commands to retrieve data from a database within a business layer (or middle tier). That way, I always have a one-liner within my page code. This makes it very clean and simple to maintain.

Anon C. what do you think your code will do when i throw this "' or 1=1" at it?

OMG YOUR ELITE HACKING SKILLS HAVE HUMBLED ME!

Or, we could just escape your quote, as any competent developer would already be doing. Just ask Mr. Michael O'Hare how he feels about single quote escaping.

The newlines are likely there so that, if you turn on logging, the query is easier to read in the log. I don't see anything wrong with that.

Not putting the SQL into a stored procedure: this can save a *lot* of configuration management headaches. If you have multiple versions of the software deployed, you don't need to have versioned stored procedures. And you don't need a system for proving that the stored procedures in your production databases are the same as the stored procedures in your test database. (You *do* use a separate database for testing, right?) Nor do you have to do separate database loads when doing a new release. (This is important when you use methodoligies like XP, where you may release several times per day.)

As for not mixing SQL and program code, I don't see why people have such a problem with it. If you have two closely related pieces of code (the SQL and the other stuff that processes it), why on earth would you not want them as close together as possible. Your programmers are too productive and you want them to have to go digging around in separate files? If you want to more easily identify what to change when you change databases, don't do it by putting all the stuff you hope will break in a separate file: do it the proper way and write some automated unit tests. (You *do* have a comprehensive suite of automated unit tests covering all of your database stuff, right?)

This whole "don't embed SQL" thing strikes me as a lame attempt to deal with issues caused by bad development habits in the first place.

Oh, don't take any of the above as an attempt to justify the query itself. A 150-odd line SQL query sounds rather dodgy to me, though I can't see enough of it to decide just how bad it might be.

Did you know that MySQL cannot use stored procedures? So, yeah i end up writing code like that. _BUT_ i parametrize it :). In any case, i have a whole file for the SQL thingies. So i have separated layers of code.

Still... this megacode example makes me thing it was automatically generated with some third party tool.

After all... where many dollars are at stake, we must remember: "If it ain't broke, DON'T FIX IT!".

This code is not an argument for the use of stored procedures. The SQL would be almost as bad in there. If you are going to put it in the database, make a view.

It obviously doesn't belong in the code inline. A lot of the other solutions here make sense. O/R Mapping or code generation probably make the most sense. It could be externalized, long strings in general make for brittle code. I would at least stick it in a method so it could be overridden if need be.

Stored procedures are just not the answer. Stick with triggers and views, and your database will be happy. Let your DBA start producing mounds of code and you project turns into an EAI nightmare.

I find it funny that without any context, you guys can look at one chunk of sql and pat yourselves on the back. We're all such efficient programmers when we're looking at someone elses code.

Sheesh! The only thing truly wrong that I can see, given the slight background that accompanies the posting, is that it seems some poor schmuck is expected to produce a dynamic reporting system sitting on top of some effing 4nf database! "BUT HE SHOULDA PUT IT INA MIDDLE TIER! THERE"S BUSINSESS LOGIC BLAH BLAH BLAH"... shutup. (by the way, that is not my sql up there...:)

ps. I'm sure asp.net programmers produce lovely ide-generated code...

For the example in that WTF I can admit that here it shouldn't be used a stored procedure... that would be just a lame approach...
But, having virtualy no experience in real/hot-shot programming and based on my limited experience with some Eastern-Europe made ERP on MS SQL Server 2000, I can tell that some well conceived and well programmed stored procedures can save a lot of trouble throughout the version "upgrades". Meanwhile, some other poor written SPs can be a huge pain in the *** when it comes to "major version upgrade", even when the upgrade package is quite automated...
Also I have to admit that for the most common queries, in-line code is better than a lame SP or view. But, when it comes to business logic, you better rely on well written SPs and views, 'cause it's a lot easier to put the DBA at work to modify and/or debug a SP or a view to meet your needs than patching dozens of user's modules... Of course, that applies to well-designed databases...
Anyway, that's just MHO :D

captcha = genius ... I'm NOT

Anonymous:

For the example in that WTF I can admit that here it shouldn't be used a stored procedure... that would be just a lame approach...

Um, why would a stored procedure be lame? It's a large query (ok, so maybe the query itself is pants but on the assumption it does actually need to be that big) therefore it should be in the database as an SP or view so that it is precompiled and to ensure that the access paths are stored. In-line SQL will not be precompiled and so there will be a performance hit, especially for large queries.

Saying that, a view is out in this case because from what I can see it takes parameters (well assuming that's why variables are used for the where clause on the join for business_calendar.

I'm getting real tired of this glorification of SP's dividing business logic into separated makes an enterprise system even harder to debug, and maintain. And this precompiled myth is a hard one do kill: First of all, the performace problems are in almost all cases not depending on whether or not the query is inline, in a view or SP. It is depends on how the query is built, tables, indexes etc.
Second: The benefit of using 'precompiled' SP's , at least in SQL server and according to microsoft, is no different to inline querys see: http://msdn.microsoft.com/en-us/library/aa174792.aspx

find for all kinds of watches and women handbags

A Lange & Sohne watches
http://replica038.com/a-lange-sohne-watches.html

Audemars Piguet watches
http://replica038.com/audemars-piguet-watches.html

Ulysse Nardin watches
http://replica038.com/ulysse-nardin-watches.html