|
No, there's more to it than "poorly written anything is junk". The problem is structural in nature, not just a matter of bad coders.
The problem introduced here is actually very similar to "DLL hell". An application's domain of code-based dependencies and interactions is well mapped and tracked by modern programming tools. Typically you define a "solution" or something along those lines, with all your code together tracked in source control, and have a build process that carefully lays out what is dependent on what.
Stored procedures are an aberration from this entire model. They're dependencies that you don't build in, they sit out there somewhere outside the same domain of control and tracking, and they change. Or they don't, and they become one-use things that build up in a giant, unstructured list, violating both notions of code organization and code re-use. They're like web services, in that they are exposed for use, and then you never really know who or what is using them, and so they are really hard, compared to regular code, to be confident in changing or eliminating. All kinds of applications, services, and other database objects could have come to rely on an SP over time, but the dependencies are invisible, and it takes a lot of work to track them down, and sometimes isn't even possible.
Don't get me wrong, SP's are actually important, for performance reasons, for code safety reasons, and so on. But they definitely break the model of good code practices that we all want to follow, not breaking it just because of coders being good or bad, but because these things exist in a different arena outside the bounds of the models and practices that good programmers put in place to manage their projects.
They also make themselves frustrating in two other ways.
#1, They lend themselves to obfuscated code in a way that regular programming languages do not. SQL is designed as a query language, not a procedural, object-oriented, or business object modeling language. Things that are perfectly clear and can be documented and written very clearly in, say, C#, are very messy in SQL. This would be okay-- use each language for its own best purpose-- except...
#2, They nearly always wind up including more than just data queries. Admittedly this is partly-- but not completely-- an architectural or coder-dependent decision, but SP's tend to absorb business logic and destroy the multi-tiered design principles that you worked so hard to keep to. You wind up with a design that looks like this:
[Presentation] <--> [Business logic] <--> [Data Access] <--> [Hybrid Raw Data/Data Access/Business Logic Layer].
That's even worse than the normal, unavoidable slight overlap or crossover between adjoining layers; the business logic appears in two places that aren't even next to each other. Furthermore, now your business logic belongs half to the programmer and half to the DBA, and you lose one of the major purposes of having tiered architecture in the first place, which is the ability to, as much as possible, contain all related concerns in one tier that can be modified (largely) independently of the others.
And you also have now just removed your business logic-- arguably the most important part of the application to control carefully and track changes for-- from the application's main area of source control and issue tracking, unless you are strictly considering every stored procedure to belong to exactly one and only one application, somehow enforcing this, and doing extra work to try to keep good track of them in the same source control system (made easier with some systems than others). (And even then, you're stuck dumping the stored procedures into a giant bin, where you're likely to have naming collisions and end up with a gigantic list than nobody wants to venture into to find something.)
You're also removing from the developers the part that most logically should belong to them. They are developing applications, which means they are designing how things work, which means they are primarily existing in the business/application logic realm. For some types of applications, there are designers who do a lot of the presentation layer, and DBA's should be heavily involved in the DAL, but developers are there to make the application work right. If you put lots of business logic in the database, then you're handing off stuff that developer/analysts should be good at, to the database admin/analysts, who are supposed to be good at other things-- namely, storing and extracting data.
Of course, you can't be perfectly pure about anything in real life. But as a general rule, stored procedures should be there to return data, and they should be designed to do that well and quickly and efficiently. And yes, arguably they do need to understand business objects to the extent of being able to maintain the integrity of a data store. But if they get too mixed up in the jobs of trying to be the application, things get way too messy.
|
|
|
|
|
Have a 5 from me for perfectly expressing the point I was trying but failing to make.
-Rd
Hit any user to continue.
|
|
|
|
|
Yeah, I pretty much agree and haven't used stored procs hardly anywhere. I also try to use very generic SQL when ever possible to avoid problem swapping out databases.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Except when it comes to security - isolating code that changes the database into stored procedures that accept parameters helps guard against some of the most common security violations - SQL injection attacks, and far too few coders seem to appreciate the effort required to adequately ensure that SQL statements build and submitted to the database are adequately cleansed to prevent this.
This scenario comes to mind: Little Bobby Tables (XKCD)
|
|
|
|
|
Rob Grainger wrote: SQL injection attacks ... far too few coders seem to appreciate the effort required to adequately ensure that SQL statements ... adequately cleansed to prevent this.
It's not that hard, is it? Provided you know what the string delimter and escape characters are, there's no problem. For example, in SQL Server, escape all string parameter values by doubling up any single quote character (thus escaping them), and wrap the whole string in a pair of single quote characters (thus defining the limits of the string to the parser), and you're sorted.
|
|
|
|
|
...or just use Parameters instead - it makes the code easier to read as a bonus...
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
|
|
|
|
|
Could you supply example code of how to use parameters with an IN clause, where the number of items in the in clause is not known until runtime?
I've never seen a solution to that one...
|
|
|
|
|
Not without thinking about it for a while!
But if you are doing things like that, you should know what you are doing anyway rather than just suck-it-and-see which most seem to try. 
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
|
|
|
|
|
You might be thinking for a while. As far as I'm aware, it's not possible.
|
|
|
|
|
You can't use parameters with an IN clause.
You would have to use something similar to "WHERE ((col1 = @parm1) OR (col1 = @parm2)) ..." which is easy enough to build at runtime.
|
|
|
|
|
True, but it rules out stored procedures...
|
|
|
|
|
You could use table value parameters to pass multiple values in one parameter, I don't use stored procedures for most projects though.
Unless I am going to be performing the same query from 2 different applications or the query is extremely complex I always use paramaterized queries.
|
|
|
|
|
You could try using a table variable to essentially perform the operation.
Add the values to the table variable and join on the table. 
|
|
|
|
|
Or you could just use some standard SQL, with properly escaped parameters, which was my original point. Much simpler to develop and debug.
|
|
|
|
|
I disagree, I don't think you should be escaping characters yourself.
|
|
|
|
|
|
Different DBMS's have different escape characters and it's just not a nice way of doing it.
Paramaterized queries were created for a reason.
|
|
|
|
|
ScottM1 wrote: Different DBMS's have different escape characters
True. They also have different stored procedure syntax. Are you suggesting that's a good reason not to use stored procedures?
|
|
|
|
|
That's not what I'm suggesting at all.
If you were using stored procedures the procedure syntax would not change the way you call the stored procedures from your application.
If you were building SQL queries in your application and escaping it yourself different escape characters would force you to go through every single one and change it.
What happens if you forget to escape one of the arguments before you pass it?
Read this[^]
|
|
|
|
|
So if I change the database, I have to change one function (note: not "every single query") that escapes out strings. If you change database, you (possibly) have to rewrite every single stored procedure.
ScottM1 wrote: What happens if you forget to escape one of the arguments before you pass it?
Then you have a security bug.
Newsflash: If you don't write code correctly, it has bugs.
|
|
|
|
|
Electron Shepherd wrote: Then you have a security bug.
No, then YOU have a security bug, I don't have to worry about this.
The fact of the matter is you shouldn't be escaping characters yourself.
Parameterized queries were created for this purpose so why would you want to even do it yourself?
There are also performance benefits to using parameterized queries.
|
|
|
|
|
ScottM1 wrote: The fact of the matter is
Opinion, not fact.
ScottM1 wrote: so why would you want to even do it yourself?
And if you need to uyse and IN clause? What do you do then?
ScottM1 wrote: There are also performance benefits to using parameterized queries.
Really? As they say "state your source". Have you measured them?
|
|
|
|
|
Electron Shepherd wrote: Opinion, not fact.
Fact
Electron Shepherd wrote: And if you need to uyse and IN clause? What do you do then?
I already told you in a previous post.
Electron Shepherd wrote: Really? As they say "state your source". Have you measured them?
A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
|
|
|
|
|
"The fact of the matter is you shouldn't be escaping characters yourself."
That is an opinion, held by you. You are perfectly entitled to hold that opinion. However, the one thing it is not is a fact.
ScottM1 wrote: A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
Is that true? I don't see how it can be. I thought they were compiled each time, since a parameterised query is an ad-hoc query. All you are changing is how the variable parts of the query reach the DBMS.
|
|
|
|
|
It is true.
Parameterized queries result in prepared statements whose execution plan is re-used with different parameters, this is because it basically calls the system stored procedure sp_execute.
An adhoc query will only use the same execution plan if the query is exactly the same including all parameters and whitespace characters.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
