Piecemeal Deployment

There have been a lot of arguments about the relative benefits and costs of using stored procedures versus making direct inline sql calls.

The argument used to be that stord procs were faster (less sent across the wire from client to server plus precompiled results).  This is less the case these days with better caching technology in the databases.

Another argument is the security one.  If the application only allows the end users to call the sprocs then there is a much smaller surface area to attack/abuse.  If the users can call sql directly it only takes one technical user to start making direct calls from excel (or another freely available client tool) and one user can cripple server performance with a careless query.

Sprocs also make a great place to put business logic that needs to be identical across multiple client applications – especially if they don’t share a codebase (At work we have an inhouse app and an extensible third party application that need to call the same code).

Sprocs also make for very clean bugfix deployment.  Since you can freely replace it in a single place (per site intallation) this makes for a low cost option – compared to an application rollout which could affect hundreds/thousands of machines.

I wonder if that is the real attraction of a web application development.  You can freely replace a page without kicking out all of the users (at least that is the attraction of PHP – not sure that asp.net allows that much flexibility).