I've got a good question here.
What are the performance differences between accessing a database to query using ASP.NET CODEFILE against using SQL STORED PROCEDURE
For ease of use, coding the query is easier, esp when coming to maintanence and changes.
How ever, a stored procedure is compiled in a database and run by the database!
Which one is more efficient, better to use?
SQL Server caches the execution plan of any query, SPROC or not. There is virtually no difference here. Basically, you save sending the query text over the network when using an sproc, nothing more. Source: http://msdn.microsoft.com/en-us/library/ms181055.aspx
Without and special reason being present, do what ever is more convenient to you.
The other answers suggesting generally better performance for sprocs are not true.