Share via

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

  • Article

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.

Comments

  • Anonymous
    June 29, 2008
    Thought provoking. Recent developments like LINQ provide an even more compelling reason for this shift in paradigm .

  • Anonymous
    June 30, 2008
    The comment has been removed

  • Anonymous
    June 30, 2008
    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 :)

  • Anonymous
    June 30, 2008
    The comment has been removed

  • Anonymous
    June 30, 2008
    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?