I've got a data access layer which utilizes the Enterprise Library.
Now I'm wondering, what's best: Using saved methods to produce my SQL, or writing SQL strings inside the data access classes?
I'd always use Saved Methods, Functions, Sights etc instead of keep your SQL within my Data Access Classes.
If there's a little bug within the SQL, I've found that it's much simpler to alter a Saved Proc around the SQL Server than rather than alter the class and also have to rebuild and deploy it.
Plus there's the additional advantage of caching and SQL Server Security.
Little bit of a classic article, but well worth the read.
These bankruptcies are not the only real two options if you're developing using C#.
It's also wise to take a look at Entity Framework and NHibernate to determine the things they offer - because they possess some advantages in addition to both Saved Methods and SQL Strings.
Whichever route you decide to take, the most crucial factor is keeping everything separated. Create objects to represent your computer data and employ a repository to complete any interaction using the database, including changing the information in to the objects to pass through to the application. Which means that should you convince you about SQL strings, saved methods or ORM frameworks - it's not necessary to impact your whole application - you'll simply need to replace the repository.
Repoisitory Pattern: http://martinfowler.com/eaaCatalog/repository.html
Entiry Framework: http://msdn.microsoft.com/en-us/library/aa697427%28VS.80%29.aspx
It is dependent!
SP's have the advantage of better security and faster execution occasions. Strings have the advantage of rapid development and talent to create changes very rapidly.
To reply to your question, neither is actually 'better'.
You will find several aspects towards the answer: Performance, how good will it react to changes, and just how fast can new person determine what's where without interfering with solution, development time, testing....
For performance, saved methods possess a better execution time since they're precompiled as well as their execution plans are saved. The Sql text is put together every time it's going to be performed (but, the majority of dbms are storing the plans for every query text which means this impact isn't that drastic).
How good are you able to maintain that solution? I have had done some relatively small projects (<40 tables), which had thousand of saved methods - it had been a real hell to change anything because you can't understand what other sp it'll affect.
For some mapper (like entity framework or similar) you'll have to discover the tool first, however it takes care of - no writing sql, and when your dbmodel changes the mapper can update it's organizations instantly so there is no "invalid column title" or similar errors.
Final point here is - for those who have time implement some ORM. If you do not then only use plain sql. Use saved methods only in performance critical procedures.
Hope it will help.