Stored Procedures for every little query vs. hard coded sql queries [closed]

Question

I'm writing a .NET application and was wondering... should I really write a stored procedure for every query I have, or is there some role of thumb here?

I know the benefits of writing SPs (like security, not having to recompile code to change a query, query compilation).

But I find myself many times in situations where I just need a simple select or delete operation against my DB (really really simple stuff - with not parameters) - so what do you think is better - writing a stored procedure for each and every query or having some queries hard coded? I had some projects where I found myself with a great deal of stored procedures just because of that...

Thanks

Answer 1

There are a few options cited for stored procedures:

• performance: this hasn't been true for ages - these days sprocs and parameterized raw commands share the exact same cache
• "the dba can change it if needed" - if the dba changes code without it going through a proper deploy process, I will more than shout at them - and if your c# is harder to deploy than 3 clicks: fix that
• security: this one actually does have some merit, but only in some very limited scenarios like banks; and note - the point here is that the calling account sometimes doesn't have any access to the raw tables: just sprocs. Most of us don't work in this environment

Personally, I rarely use sprocs. Raw SQL is much more flexible - and sensible c# code can write appropriate SQL dynamically. You can do that inside SQL too, but it isn't a good fit. You also get to use tools like ORMs. More importantly, it avoids a huge deployment problem: the chicken and egg sproc change. If the sproc change is major, you can't deploy the sproc without breaking the callers, and you can't update the callers without updating the sproc. And you have multiple servers calling a single central db. This means that you need to carefully coordinate changes such that both ends are happy. If the query is in the c# this simply isn't a problem: as you update each individual server, it is by definition using the version of the query it expects.

Basically, I'm a huge fan of direct parameterized SQL these days.