Object-relational integration people. You don’t put business logic rules and validations in the database. Oracle would love to compress the whole N-tier world down into the database engine but it’s not needed. Stored procs are useful IF you need to grant individual users security access to specific limits things. However, in practice, most large scale business systems use a generic unrestricted ID to access the database from the app layer and apply access rules there anyway. If the data universe is encapsulated in an object-based access model that hides the underlying relational db structure and uses parameterized SQL to interact with the db engine, that’s all you need. Stored procedures then become an unnecessary maintenance annoyance with little practical performance benefit.
The majority of this is simply retoric from MS from a time before there were proper ways of handeling re-use at the database level, that is repeated blindly by the MS SQL masses.
Having a long standing background in Oracle as well as MS SQL, and being extensible capable in both PLSQL as well as TSQL, I can safely say that I have yet to see a single argument in over probably 6 years now, that could convince me there is a need force everything through SPs.
Performance is a load of garbage. Before you people keep repeating this, test it out yourself sometime. Write a program that executes a parameterized stored procedure, and a parameterized sql, and time it as you execute it over a loop 1000 times. Let me know which one wins (hint, it might not be the one you expect, especialy if your querey has any complexity at all).
The fact of the matter is keeping your DAL in your stored procedures is now the lazy part. Get off your butts and write a meaningful API that can have multiple concurent versions, support modern language constructs, participate in actual OO design (as you can write your API in an actual language), access multiple data sources, support distributed and grid models, follow accepted design patterns (abstract factory anyone?) and lead towards a true SOA.
Just because someone told you 5 or 10 years ago that SPs are good doesn’t mean they are. We’ve moved on from that ladies and gentlemen.
Oh, and please people, seperating your database code from your application doesn’t make something “OO”. (Read the above posts, it happens too often). OO is a form of seperation of concerns, it is NOT seperation of presentation layer 
geez.
heh, I must add, ORM isn’t the answer either. Please please be careful with this technology with any sort of mid size or larger project. It looks so promosing to start, and before you know it you’re giving up flexibility, functionality, performance and maintainability, and you’re not really getting any benefit out of it.
I know it looks so promising up front… but please, just be careful 
how to write stored procedure in mysql
Stored Procedures are written in big iron database “languages” like PL/SQL (Oracle) or T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy, incoherent design choices that always result from the torturous evolution of ten years of backwards compatibility. You really don’t want to be writing a lot of code in this stuff. For context, JavaScript is a giant step up from PL/SQL or T-SQL.
Stored Procedures typically cannot be debugged in the same IDE you write your UI. Every time I isolate an exception in the procs, I have to stop what I am doing, bust out my copy of Toad, and load up the database packages to see what’s going wrong. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.
Stored Procedures don’t provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL or PL/SQL exception handling, we get cryptic ‘errors’ returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?
Stored Procedures can’t pass objects. So, if you’re not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter-- either too many, not enough, or bad datatypes-- I get a generic “bad call” error. Oracle can’t tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.
Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can’t view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.
When an application is made by a software house (take note!) adhering to CMMI L5 SP choices (why are other not focusing on User-Defined Functions? it is really most taxing to study another language again in the RDBMS) are debated just on the Business Analysis Phase from among other choices. Designs, backward compatibility, languages of choice, technology, RDBMS among others. So any blames will be passed on the Business Analysts and not on the developers.
(document everything)
When an error is incurred within the embedded code of vb or java or c and SQL, the error can not be located since a programming language are not made to check line per line in the SQL statement embedded with it. The enterprise environment of SQLServer can do the same and save the SP/UDF without syntax error. SP/UDF requires specific error handling that may be even longer than the SQL code itself. (document everything)
Codes are not supposed to be rushed, they should be thoroughly tested and there should be separate team just to test the codes. Specific error testing codes should be inserted in between evaluation to prevent unlikely errors. SP/UDF are another language distinct and separate from vb or java or or .Net. SP/UDF receives parameters and passes return values. (document everything)
For me SP/UDF/UDT/Trigger is the only way to go since programming language takes long time to tame and if another problem with SQL adds to it, it will double or even triple the team’s development time by more than twice.
Stored Procs -versus- embedded SQL
- You have a critical, large application that must run 24/7. Whenever a critical production error occurs you have to fix it, redeploy to a test environment, test, and redeploy to production. If the issue/error happened in a stored proc … you have cut your final deployment time by about 100%. In The Real World “Time Is Money”.
- Changing C/Java/src-Code is inherently more risky than making a change to a stored procedure.
- The simple matter of testing a stored proc change in a UAT environment is so much simpler, safer and easier than testing a application-code change … silly to think otherwise.
- Bandwidth becomes a BIG issue if you have an application with a heavy thru-put and large user community. For example, the need to upload huge amounts of data and process that data should always be done using some native load utility in conjunction with a stored procedure to process the data.
- Security:
Map all users to a functional id. Map the functional id to an entitlement mechanism maintained in the database and accessible only via store procedures. Associate the functional id to a group that grants execute permission on the stored proc. - Guess everyone knows about execution plans, update statistics etc.
- Distribution of processing and io …
Response to George…
-
The only time you would save is with the deployment, unless you don’t test your stored procedures. However the main time I have seen where this happens is when people used stored procedures for everything; in cases where people put thier CRUD in the code is is alot easier to test and thoses type of situations do not happen. If you cannot catch that a select col1,col2 from tab1 vs the more complex stored procedure during development then your testing is bad.
-
it is only easier if you don’t do source control and don’t do testing. Otherwise it is far easier to change the code and submit through standard testing.
-
It is only easier because you are not using source control and testing outside of production environment. Nor are you testing that the stored procedure is used outside place that alerted you to the problem. If the stored procedure is being used in mutliple location you better test all thoses locations. CRUD dynamic programming does not have this problem, far easier to test all location since you reused the code it is for the exact reason it was originally created, where with stored procedures you tend to use a stored procedure that gives you what you want even if it gives you more then what you need.
-
While you will save a few 100 characters between sending the query and the name of stored procedure you will quickly lose it because people don’t create stored procedures for every case so you will get sent back alot of data that is ignored. For example say you have a table of 15 fields, with dynamic CRUD programming you will just select the fields you need with stored procedures you will generally have 1 stored procedure per table that returns all fields do one request with that stored procedure when you don’t need all 15 fields and any bandwidth is gone with just a row or two. Or you could create custom stored procedures for every select you need; have fun with that.
-
Not many people still do individual login account. Most permissions is done against a single account using roles to prevent access to places people don’t need to be. Users don’t have direct access to the databases and for the few commerical tools that do need this requirement usally work best with access to the tables and have problems using stored procedures for everything.
-
As a DBA I could care less about looking at stored procedures for determining indexes, etc I use the logs to see what is actually being used and base tuning on that. Also since MS-SQL Server 7.0 dynamic SQL and stored procedures are not processed for excution plans any differently. What stored procedure do give you is the queries will be similar enough that the same execution plan could be used; I use a development framework that help ensure that on the code side.
-
CRUD stored procedures are bad for processing. ISNULL and COALESCE take huge amount of CPU vs a straight CRUD command and and also very slow to execute.
I agree totally about the pain of coding in database languages. I am an experienced programmer but relatively new to databases and started experimenting with stored procedures recently. I wrote a routine in T-SQL to determine if a string was a valid IP address or not, and it reminded me of writing BASIC as child.
Maybe I’m just spoiled by scripting languages (Python, Perl, PHP) with regular expression support. It must have taken 20 lines of code to iterate through the string character by character, etc. Something that would take one line of code with a regex.
If you are doing alot of stuff with regex in MS-SQL server there is a decent regex package for version 2000 on code project http://www.codeproject.com/managedcpp/xpregex.asp
If doing it in version 2005 then it is really simple as shown in this example http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx also the demos that come with MS-SQL server or one of the add on development samples has all the code for doing it.
All the parameter depends on the way the stored procedure is defined and the expertise of the developer in the stored procedure. If you dont use a proper exception handling mechanism, you will be in a mess. Data validation on table data would always be done in stored procedure. It only have a more control over the data in the tables than Java or .net code.
You are SO RIGHT, Jeff Atwood!
I’ve been reviewing a stored-procedure-laden application for my company… You would not believe how much time is wasted writing, debugging, installing, compiling, and trying to figure out what else is wrong with a (hand-written, several megabyte) package of Oracle procedures.
Let’s take the cost of our time writing and debugging these stored procedures and call it X. Lets then take the savings of not writing and debugging these stored procedures (with a substitution of NHibernate or another SQL-less ORM tool) and call it Y. Let’s take the cost of processing power (e.g. a web farm) that may be required because the app isn’t utilizing the efficiency of stored procedures and call that Z. If Z - Y X Then it’s safe to say that you should never use stored procedures. Z - Y X has been true in all scenarios that I’ve encountered.
RE Who needs Stored Procedures, anyways?
your attempts to pontificate intelligently do nothing to mask your ignorance.
please head on ; I can make a good living cleaning up the messes you genius’s make.
“can not think of one VALID reason for using messy inline code, Not one, using SQL in the db gives you Scalability for one, Security and a lot easier to use.”
There are plenty of good reason not to use stored procedures for your CRUD, just read the longer posts in this topic. However lets look at your reasons.
Scalability:
Yea you can build out your databases servers to increase scalability it is expensive both in hardware and the licenses, far more then to do a web server or client machines. Also once you get into very high end database they recommend you remove the following items for scalability: Stored Procedures, triggers then joins.
Here is a article about the ebay setup from SD Forum 2006 where they say to scale databases remove the stored procedures.
http://www.addsimplicity.com.nyud.net:8080/downloads/eBaySDForum2006-11-29.pdf
Security:
You can make the case for this for client/server program, but there are ways to decrease that. It is even getting less of security risk because companies are requiring secure communications for all connections.
For web and similar applications it offers no security because the user never has any direct access to the database and has no way of access the database.
Ease of use: How?? The tools suck for editing and testing stored procedures even most of those expensive 3rd party ones, maybe visual studio team for dbas is better, have not had a chance to use it yet. They lack the ability to link in with source control, easily switch version, modern debugging capabilities along with the previously mentioned editing and testing tools. What you usually end up with editing outside of the database, submitting the text file, and then running a test script.
From the management standpoint it is not easy to use, you don’t know when they are no longer needed, and if you need make an easy change, say the sort order, you need to create a new stored procedures or add a new level of complication to the stored procedure so that it will return different results depending on how it is called; just don’t forget to test all the ways it was previously called.
I’m completely on SP’s are bad. Here are some of my personal observations.
In virtually every organisation I’ve walked into where SPs are policy because of ‘security’ I’ve seen DBAs, developers and MI experts have complete access to the underlying data so they can run reports, monitor applications etc. etc.
Virtually every DBA I talk to goes on about business logic in stored procedures; HELLO breaking every rule of good programming: your a database not an application the only concern of a database should be storing data the business logic should be in the app.
Stored Procedures break single resposibility (A change in code means a change in 40 sps means a change in the tables blah blah bugs bugs bugs).
Scalability and speed: I have seen some well complex cycles between apps and stored procedures, creating round trips, using temp tables doing some truly horrible stuff that wouldn’t be nessasary if you just did DynamicSQL. An example: how about selecting data based on a set of results a get me all records in this list I’ve just selected. What SP’s don’t support arrays? What I have to create a comma delimited list and then send it to do the sp which splits is back up into a temp table and uses a cursor blah blah bugs bugs.
Also SPs are Vendor lock-in. Just migrating from SQL 7 to 2005 has created a nightmare for our DBA team and imagine if we went to Oracle: the differences between the features of each RDBMS are HUGE meaning complete rewrites for each database. Is it me or is that just backwards?
Shorter queries: Change the customer name in an object dynamic SQL sends UPDATE customer (customerName = x) rather than having to send every single value of the customer across to the database. I cut my queries down several hundred bytes thanks to dynamic sql.
Security: anyone heard of application roles? You don’t have to grant every user access to the tables just the application. Only the application can access the database - surely thats better security not worse.
Every application with SPs I’ve ever worked on has had 90% of it’s bugs, security and upgrade issues in the SPs.
SPs can be good though. Sometimes it’s the best way but in 90% of cases databases are a way of saving data for an application to use so why make life so much harder by slowing down development and introducing extra complication by denying the application to choose the best way to access the database.
Another thing.
First the distinction between hard coded inline SQL and dynamic SQL. DynamicSQL requires very little hard coded SQL and with a good O/R tool it is optimized. I can do this: Customer.Get.Where(Age).Is(GreaterThan(18)).Is(LessThan(5))
Rather than:
command.create(“sp_GetCustomers”)
command.paramaters[“minAge”].Type = DbType.Int32
command.paramaters[“minAge”].Direction = Output
… blah blah
resultsSet = command.Execute()
… loads of code to turn results set into a Customer object.
What looks clearer/is easier to read/change/maintain?
Also how many times have DBAs changed SPs to introduce bugs. And when was the last time that this happened and the DBA didn’t use source control. Hell, does a DBA even KNOW what source control is? So even if my SQL is hard coded at least its saved somewhere I can do a diff on it and get it back.
Hello, this discussion is very old - but not yet outdated!
I noticed that no-one answered the question about transactions?
You haven’t talked about Linq/ASP.NET vNext much on your blog - I’d be interested by a debate on the subject. From what I understand it could be the ‘next big thing’ - I certainly hope so.
I firmly believe that code that can be generated shouldn’t need to be generated - it should be part of the language/framework. So as the previous poster says - I’m very much against writing embedded SQL, but generated SQL doesn’t bother me at all.
I might add that I suspect that a lot of the ‘anti’ arguments are just a cover for people who dont’ like/understand sql. And that the ‘for’ arguments are DBA’s who don’t want to share their power!
As someone who wears both programmer and DBA hats, I’m all for stored procedures, for all the reasons already listed above.
I’m from a programming background so I certainly do use source control on all changes to the database. I suspect the percentage of DBAs who don’t do version control is smaller than the percentage of programmers that do row-by-row logic rather than set based when writing their SQL code. It’s a mentality that can be hard to shake.
As far as transactions go, transactions are transactions. They should be handled either by the dynamic code or stored procedures, but I think that a DBA is going to realize the need more than strict programmers who often aren’t as concerned about using all the data-integrity techniques that are already built into a database. I’ve seen far too many folks reinvent the wheel over and over. Business logic and general data integrity belong in the database.
dietrich ( excuse spelling of name ).
I think that embedded SQL in conjuction with some type
of ORM tool such as Hibernate has its place in any environment.
But that place is very limited in functionality.
I know that we use both in my enviroment and I can tell you
immediately that the SP calls are much more efficient than the
Hibernate usage in a wide variety ways.
- Less data going across the network.
- Extreme localization of data base DATA
- Much Easier to migrate to and MIX various database management systems.
- Extreme granular entitlement is possible … ONLY … thru the use
of stored procedures. Well … that is a big statement. Of course you can create some complex form of entitlement using a combination of LDAT and Code … but I find that this is much more complex that using stored procedures to handle data entitlements, taking the burdon away from your Code.
I have coded ORMS going back 20 years and nothing has much changed; funny that. The last 6 years I have done more stored procedure work for performance and code reduction on billion+ record scientific databases. Our databases have dozen of applications old and new written to them in different languages and platforms. Business rules in the application would be a nightmare! Since our databases don’t have to be portable we take full advantage of a database vendors extensions. I’ll give one example: 6 years ago I wrote 3000+ plus lines of C++ code to provide automatic quality control of atmospheric data. Last year I wrote the same application in PL/SQL (95% SQL) in less than 500 lines and it runs 10x faster. For bonehead simple CRUD you can go either way but with real heavy data processing work and statistics etc., I have always been surprised how a database centric design will save time, money, and tons of code. We have SQL here that I cannot even imagine coding in Java or C++ since a relational set orientation to data is just a plain better way to think about the problem then OO; OO is not a decent data model and I have done lots of both.
Just a quick comment. I know the comments are almost inexcusably long already…
Someone towards the beginning mentioned that using stored procedures adds another layer. In my opinion, this is a GOOD thing. It serves as a buffer between the data and the data access tier of your code. If you know anything about OO programming, you understand the need to encapsulate. You don’t get this with dynamic or inline SQL.
Not to mention that if you have a schema change or database refactoring, you have to scour your code to see where it is broken. If you use stored procedures, you need only change the procedures to correct the output to what is expected instead of searching through all of your code. If you know anything about OO programming, you understand the need for interfaces and how much of a pain it is when you break an interface. With dynamic or inline SQL, the nightmare is doubly painful. This comes from experience.
That being said, dynamic or inline SQL is not a BAD thing. I am not one of the Stored Procedure Nazis. Sometimes you just don’t know what the query will be until you’re in the middle of code execution. However, this is more the exception than the rule, and it should be treated as a special case.