No more stored procedures

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...

mfhobbs

hmmm... maybe the semi-colon is not general across databases. Perhaps detect if SQL Server before using it otherwise you may have to use it. Also... is @@SCOPE_IDENTITY database general? If not, you may have to have 'dialects' for different databases... e.g. http://coldfusion.sys-con.com/read/43794.htm

Nick Parker

Miszou wrote:

asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do.

If that is his concern, you could look at using NHibernate[^] or ActiveRecord[^] from Castle[^], this will allow you to be database agnostic.

- Nick Parker Microsoft MVP - Visual C#
My Blog | My Articles

mfhobbs

If your guide can talk seriously about coupling and manage to conclude that exposing your relational object model to your client applications is a positive (without distracting to strawman performance discussions), I will buy it.

SHentzel

Wouldn't you want your db specific code IN the db instead of in your code? -- then your code doesn't have to change, you just create the corresponding stored procedure in the new database. Seems like this is exactly the wrong suggestion?

Trooks

Most major version control systems such as SourceSafe, CVS and PVS allow for version control of the underlying script. In fact, SQL Server 2005 is tightly integrated on a project basis with SourceSafe out of the box. So I do not understand this as a reason, but hey to each his own.

slastnoy

Except for some special cases there is no sense in using application generated unprepared queries. I always use prepared statements such as stored procedures and user-defined functions. It is more important in case with commercial projects to use prepared compiled statements, if you want to speed up your program. It is easy to transfer a stored procedure to a target server. I cannot see any reason for abandoning database logic except for some cases such as need to switch to a different database server platform in the nearest future. In case you use a complex web project that has its own engine, the database server must handle almost all aspects of your site through stored procedures and functions, that is: user authorization, gathering information about page components in case they are dynamically loaded depending on which address you query (it may be the same file with a different page id or address) and the info about them is stored in a database, page title, menus, navigation bars and other commonly used parts of a web page.

Grimolfr

David Stone wrote:

That's the dumbest thing I've ever heard.

Not quite the dumbest, but it ranks pretty highly.

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM users WHERE clue IS NOT NULL GO

(0 row(s) affected)

Blexrude

Your bosses idea that possible changes of the back end databases is a reason to go with in line SQL is flawed. This is all the more reason to use stored procedures. What if you use a function in your query that does not exist on the other db platform? This is easy to get around if you use SPs but if all your SQL is compiled guess what.... Is your boss a developer?

Jim Blexrude Senior Software Developer/Consultant

Grimolfr

@@SCOPE_IDENTITY doesn't even work on SQL Server. It should be SCOPE_IDENTITY(). (It's a system function, not a global variable.)