Welcome to MSDN Blogs Sign in | Join | Help

I've decided that I finally really give up on stored procedures

As much as I want to play the game and use stored procedures for data access logic, time and time again I run into yet anothe rissue where I get bit. I've always pushed back on developers who want to use stored procedures for any number of reasons. There's a great list of reasons here. This time it turns out not to be the usual reasons - this time it's much simpler: there's really no way to make the whole deployment story scale in any way. As soon as you have more than one database (i.e. a multi-tenant application hitting a "private" database) you WILL run into a problem when you need to fix the always present data access bug.

Ugh... why can't I have a single place where I can put all my data access logic? Like, maybe, just maybe, a shared database holding no structure, only proc definitions? Or, maybe a DLL. Yeah, that'll do it, I'll use a DLL with dynamic SQL.

Published Sunday, June 29, 2008 8:31 PM by mikemill

Comments

# re: I've decided that I finally really give up on stored procedures

Thought provoking. Recent developments like LINQ provide an even more compelling reason for this shift in paradigm .

Monday, June 30, 2008 4:41 AM by saurabhd

# re: I've decided that I finally really give up on stored procedures

I mean you could just create a database that has just sprocs, then you coudl use those procs to use four part naming to execute procs or run your queries on other databases.

But I think that would also get messy.

Monday, June 30, 2008 12:19 PM by TJ

# re: I've decided that I finally really give up on stored procedures

That would be great if it really worked without jumping through a ton of hoops. The problem is that the proc wants to run in the "proc" database despite the context ("data" database) and the four-part calling convention. I'm open to suggestions and ideas if anyone wants to convince me to come back to the dark side :)

Monday, June 30, 2008 12:43 PM by mikemill

# re: I've decided that I finally really give up on stored procedures

I'm not sure I agree. A stored procedure is another function that you call, and it allows you to reuse those components from other applications, or other places in your application, and tune appropriately.

They don't take any longer to build than any other function you add in your application, thought it's a different process you have to get used to.

I'm also not sure how you calling multiple databases means that stored procedures are a problem? If you make two calls from your app, why not call two procedures? If you join together disparate data sources, this can be done in stored procedures.

I'd like to see a followup post on how that works.

I think a number of reasons listed in the link aren't accurate as well. Some are, but my guess is that the writer hasn't used stored procedures properly and then complains that they don't work.

Monday, June 30, 2008 3:03 PM by Steve Jones

# re: I've decided that I finally really give up on stored procedures

We're talking about different things I think. I agree that sprocs are just another language to use, albeit one with a very different build / test process.

The problem I run into is dealing with many databases, all serving the same multi-tenant application, but with tenant-specific data. In this case, think hundreds of tenants, you need to visit every database to update a given sproc if / when there's a bug found. Sure, it's easy to write an automated script to do this, and I'd recommend using one even for a single database, but it really isn't something that screams "great engineering practice". You wouldn't have 100 copies of an assembly on a machine, one for each customer, would you?

Monday, June 30, 2008 3:55 PM by mikemill
New Comments to this post are disabled
 
Page view tracker