Stored Procedures vs Parameterized Queries

guynorton · December 24th, 2011, 12:00 AM

Stored Procedures vs Parameterized Queries....

I am trying to develop good habits and practices and before I tie myself to either approach would like to hear what others have to say on the matter. It's an old debate and even after googling and reading the results I still don't know which way to go!

At the moment I favour parameterized queries.
I will be working mainly on my own projects in asp.net, and am certain my work will not be ported over to other platforms in the future.
I see no perfomance or security advantages in either approach.
Stored procedures seem to add a level of abstraction which offers little of use.
I also find it a nuisance to open up a seperate app to debug sql.

Anyone with some words of wisdom before I commit myself to my decision....?

Imar · December 24th, 2011, 07:11 AM

Hi there,

Quote:

Stored procedures seem to add a level of abstraction which offers little of use.

They indeed do add an additional level of abstraction but not without offering additional benefits. What I like about stored procedures is reuse and maintainability. It's a lot easier to make a change to a stored procedure than to make the same change in code as you don't have to deploy the entire application.

Quote:

I also find it a nuisance to open up a seperate app to debug sql.

Not sure exactly what you mean with debug your SQL; if you mean manage and change your code, you can do most of it with the built-in tools. In addition, the upcoming Juneau project may bring SQL development closer to Visual Studio:
http://msdn.microsoft.com/en-us/magazine/hh394146.aspx

Based on your description, I'd say either option works. Just pick what you feel most comfortable with.

Cheers,

Imar

guynorton · December 24th, 2011, 07:50 AM

Hi there Imar,

Well if it's the way you do it, then it'll be my way too!

I'm sure I'll experience one of those eureka moments (of which I have had many this last year) when it all suddenly makes sense....

regards, Guy

Imar · December 24th, 2011, 08:10 AM

But what about EF though? With EF I hardly have the need to write any paramaterized query or stored procedure at all. Granted, you lose the option to make changes without deploying but you get a lot of power and flexibiliy in return.

Especially EF using Code First gives you a lot of power, at the cost of a pretty steep learning curve.

http://msdn.microsoft.com/en-us/data/aa937723
http://blogs.msdn.com/b/adonet/archi...lkthrough.aspx

This is "just" an alternative; you don't have to do Code First when using EF, but it's a model I really like.

Here's to hoping you'll have many Eureka moments.... ;-)

Imar