No more stored procedures

[Stan Klimoff]

I agree. SP improve performance only in terms of round-trips; there's no speed difference between a call of an SP or a plain-text SQL execution -- of cause, you can invent a special case when a common repeating action is accomplished by a-completely-dynamic-sql-with-a-unique-execution-plan. My experience is limited to SQL Server though. I guess there are DMBS that respect the difference. And, IMO, every major DBMS has an SQL only in respect to the fact that the DBMS itself is a standalone product. It would be weird to sell a DBMS that needs a set of programming tools to make something-not-so-trivial. Also, SPs make the DBMS interaction language Turing-complete.

[Russell Jones]

last year I worked on a web based system for a company that was a market leader in its field. If you used the username "admin" and a password of "' or true--" you could easily get to a reporting page that allowed arbitrary sql to be executed. Most of the clients of the company had the database running as a domain admin account. Whichever way you chose to implement your DB access, please avoid building dynamic code that allows this kind of thing to happen. I include dynamic code inside an SP that uses the exec command. Russell

[Russell Jones]

it doesn't matter if you have identical values in 2 different keys. using identities you would start with Person_id = 1 and address_id = 1 and that would be fine. if you are worried about clashing keys wrap all your important calls in a transaction and only commit if there are no errors. Russell

[Pete OHanlon]

Hear hear.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

[dazfuller]

That has got to be the dumbest thing I have ever heard, I know SQL2000 has some issues with stored procedures when it comes to using parameters (anyone wants to know what that is then let me know). If your boss is so concerned then surely the smartest thing to do is factor out all of the database calls into a Data Access Layer, then if you ever do sell the product to someone who uses a different database system then you only need to modify the DAL. Writing your SQL code directly into the application makes your application wholly dependent on the database you use and if any of your SQL objects or logic changes then so must your code. Also there is no guarantee that the SQL code you write will work in another database such as MySQL or Postgres (or even SQLite???). Using a DAL means that can all be sorted out in it's own DLL without the bulk of your code ever having to know what database system it's using. Then there's also the fact that it can be easier to manage security using views and procedures.

[Stewart Kingsley]

Its easier to convert a series of SPs, views etc to a new destination database than to have to find all the SQL in your code and convert that instead. Select @@identity will not work in Oracle (if memory serves) so SQL code conversion will HAVE to be done when moving to another backend DB. If you want the code to be entirely database independent then dont use SQL at all, if thats even possible. There is good software and there is bad software, and this is not one of them.

[MatthewTyler]

How does this help since for example @@identity doesn't exist in MySQL? So the code still won't run on a different DB. Writing code for different DBs is like writing DHTML for different browsers; easy in theory but unless you test against all the different DBs at implementation time porting a finished app is going to be a nightmare however you write the queries.

[beatle11]

From what I can gather, as it is just a small IT department I can't see there being an issue. Stored procedures shouldn't cause too many problems if you change databases as they are supported by all the major databases. It does seem unnecessary to change it but I guess either way works.

[reshi999]

I've worked as a SQL developer for 6 years and I can see where your boss is coming from - the theory is very elegant but in practical terms it will slow down processing speed Stored procedures are the key to SQL performance enhancements and I could not do without them - example: A legacy system calculating large scale posting costs was previously coded in C#, I recoded it into SQL: c# : Took 20-30 seconds to process a profile, plus had to code a custom garbage collection program over the top. sql : Takes 0.3 seconds to complete with no gc required, this was from a straight port of the code with a good indexing strat. The results speak for themselves really... But anyway I've had managers come up with similar rules (one insisted storing data as text files would be much faster than SQL tables, he got sacked soon after the benchmark figures came back) - Develop a test system doing the common operations for both using the different coding styles, benchmark them and show them the difference. One way to find a half way solution would be to code all your data access into a class, then explain that to move to another database simply requires one class to be recoded. hth.

[JHubSharp]

While not being a database expert (so I can't argue on the performance issue of SP's vs dynamic SQL), I've seen the kind of garbage dynamic SQL can create. I'm more curious as to the why of the argument. If you're selling to a client that supports MS SQL, you can easily have a script that builds what you need. If you're not, won't the syntax of your SQL potentially change anyway? Not to mention all the ways you interact with the database will have to be changed to using a different provider and such. Even using .NET and only programming to the data provider interfaces, I'd imagine a few things could still need to change. If work has to be done regardless, wouldn't you prefer to benefit from the performance (however slight) and abstraction of data logic that stored procedures provide?

[diana_m]

Well, Miszou...it seems that your supervisor needs to read the SQL Server Books Online...there is also a good article on this in the "SQL Server Standard" magazine (the March/April 2005 issue)...so...ask him/her to spend some time learning (more) SQL (and perhaps .NET?)...The stored procs are supported by any serious RDBMS - I've been told that even recent MySQL supports them... By the way, I do not understand why does your company still uses classic ASP? -- modified at 6:36 Thursday 4th January, 2007

[Marc Clifton]

Andy Brummer wrote:

If you consider the bug rate of even an exceptional developer working under an exceptional development process, the probability of the extra queries causing errors is probably more likely then getting a guid collision.

That was exactly my first thought too. Marc

Thyme In The Country

People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith

[Marc Clifton]

Rocky Moore wrote:

Yeah, I sure hope mission critical apps think about it

I love how "mission critical apps" is the ultimate "ooh, now I'm scared" buzzword to drop on people. Consider: While each generated GUID is not guaranteed to be unique, the total number of unique keys (2128 or 3.40282366×1038) is so large that the probability of the same number being generated twice is very small. Now your basic int, a 32 bit value, (4 bytes), is going to rollover much sooner than a GUID is going to collide. What does your DB do when the autonumbering rolls over? I actually tested that once. Now consider some other numbers. Even at 2^31 for a signed int, thats 2 trillion records. Hmmm. I don't know a lot of mission critical apps that will hit 2 trillion. Ever. I can think of a couple, like cataloging stars or grains of sand. More numbers. At 4 bytes for an int just to store the ID, that's 8GB if you were to have a record using every possible integer ID. Let's say, ooh, on average, another 1000 bytes for data per row? So that's another 2^31 * 1000, or 2 terrabytes of data. That terrabyte star catalog is the only thing I can think of that requires that much space. So, let's be real when we talk about mission critical apps. 2 trillion records? 2 terrabytes of disk space? And those numbers are using an integer ID and based on the range of values that it is capable of.

Rocky Moore wrote:

In a large application, they would have to be unique to maybe 20-100 tables.

No. Only the primary key needs to be unique. Not the FK's! And identical keys in separate tables isn't an issue either!

Rocky Moore wrote:

Not to mention that have no order so you end up using another field to force order.

Ummm...PK's should be abstracted anyways. You'd never order on a PK!

Rocky Moore wrote:

Little things like this bug me so bad

Well, we pick our battles. If you actually stop and think about the issue, I think there's very little to be bugged about, and actually a case more for being bugged about your mission critical apps using an integer ID. Marc

Thyme In The Country

People are just notoriously impossible. --DavidCrow
There's

[Mike Dimmick]

As others have said, it's a significant task to port SQL from one database to another. The standard dialect, SQL-92, even now has widely varying levels of support, and does not offer all the features available in any given database. Also of significance for correct implementation of concurrency is that SQL Server's concurrency is (traditionally) based on locks, while Oracle's is based on row-versioning. I use SourceGear's Vault for version control. Vault is based on SQL Server and makes heavy use of stored procedures. These SPs are encrypted (use WITH ENCRYPTION with your CREATE PROCEDURE statement). This prevents the end-user from viewing it easily (the encryption is weak and can be broken, but the user must be determined to do it). In his article, "My Life as a Code Economist"[^], SourceGear's founder Eric Sink talks about the risks of fixing bugs and addition of new features. Under the subheading 'Example: Item 6740' he discusses the use of SQL Server and the costs and benefits of supporting other servers - their conclusion is that it isn't worth it at present. Clearly the decision hasn't changed since he wrote that article at the end of 2005, since Vault still doesn't support other databases and I can't find any indication that they're planning it for future versions. We have an emerging suite of in-store applications for retailers, which use our thin-client system for handhelds (so the actual application is a plug-in to the server software, which runs on server-class hardware, rather than on the handheld computer). They were originally developed with SQL Server as the back end, that being where our original competencies lie and the database that the first customer was prepared to accept. This version used SQL Server stored procedures. However, a subsequent customer asked for Oracle support, and the contract was too good to turn down. We therefore re-implemented the stored procedures in PL/SQL, and abstracted the call interface so that the same core code could call either the SQL Server stored procedures or the Oracle ones.

Stability. What an interesting concept. -- Chris Maunder

[Siderite Zaqwedex]

I agree it's not the smartest policy ever, but it could be based on management data. For example, let's suppose that you write a nice piece of code that uses a stored procedure, then the DBA writes the stored procedure. You don't have access to the database, the DBA is hard to reach or is the "I am God" type, so the manager observes how productivity or at least bug identification and repairing is slowed down by bad communication between departments. There are other weird scenarios that I can think of, like programming for 2 weeks for a full working software, then another 2 weeks for the utility that makes sure the database is updated to the latest version and no data is lost during the upgrade or installation of the first. Also, stored procedures that use string building to execute SQL, like some I've seen, are more damaging than writing all SQL inside the code. In other words, stored procedures are cool, but they're no golden hammer.

---------- Siderite

[Siderite Zaqwedex]

That's the reason! Use a piece of code that runs a stored procedure and worry about the syntax in the database. The only reason I've found not to use stored procedures (in real life) is that the debugging is too hard. There is no reason to use ten layers and 100 stored automatically generated stored procedures when all you want is to write a single value in a simple table or something.

---------- Siderite

[Trooks]

From the managers point of view this may seem perfectly logical. However, assuming that you do not put business logic in your stored procedures it is rather trivial to adjust the stored procs for each RDBMS and in fact it is quicker than changing code in multiple locations. The real question here is not so much speed (although important) but maintenance costs and security. If a table changes then it should be easy to identify all stored procedures that access the table to update. This is far quicker than sorting though the various lines of code, classes and other mechanisms to find the offending sql string. From a security point of view (ala SQL Injection attacks) having stored procedures is better protection than dynamic SQL which the coder forgot to eliminate a rem character or character. Finally, I have worked with all major RDBMS backends and there are some differences in the SQL. The SQL however is different regardless of whether you call it in a dynamic query or stored proc. In fact it is easier to deploy to multiple RDBMS backends when you have scripts for each stored proc that are optimized for each backend language. Again, maintenance is cheaper. Let's face it PL/SQL, MySQL, or DB2 will cast a string to a date differently than T-SQL. These are easier to find when you are compiling the database scripts (yes they all throw errors on compile if they are wrong). Management should respond to the issues of maintaining separate code bases for separate back end databases. This really only works if you separate out the business logic into a separate area (usually class library) and keep the stored procs for CRUD work. My thoughts only and I will be interested to hear someone else speak.:rolleyes:

[Mcsquare]

Where I work, stored procedures are not used, but for a different reason. Stored procedures cannot be checked into a source control system and maintained under version control. On very big projects with lots and lots of programmers and multiple versions of software that are backwardly compatible, version control is a must.

[mfhobbs]

And just use one sql call: recordset.open( "insert data into table; select @@SCOPE_IDENTITY" )

[GroundSloth]

Is your company being sold or merged with another? Regardless, I don't see how avoiding stored procedures makes the code more portable to other databases. You are still writing TSQL which is not the same as other SQL implementations. I have done a side by side implementation of packaged software against both Oracle and SQL Server. We specifically used stored procedures in order to keep a single set of ASP code as the SQL was much different between the 2 databases. That's a rather strange request...